EBS QUERY TO GET THE PARTY REALTIONS AND ER DIAGRAM RELATED TO IT

ER DIAGRAM:

QUERY FOR REFERENCE:
SELECT cacct.account_number cust_account,
      cust.party_name customer_name,
      crole.role_type contact_role_type,
      cont.person_last_name contact_last_name,
      cont.person_first_name contact_first_name,
      cont.person_name_suffix contact_suffix,
      cont.party_name contact_name,
      rparty.party_name relationship_party_name
    FROM ar.hz_relationships rel
    JOIN ar.hz_parties cust
    ON rel.subject_id = cust.party_id
    JOIN ar.hz_parties cont
    ON rel.object_id = cont.party_id
    JOIN ar.hz_parties rparty
    ON rel.party_id = rparty.party_id
    JOIN ar.hz_cust_accounts cacct
    ON rel.subject_id = cacct.party_id
    JOIN ar.hz_cust_account_roles crole
    ON cacct.cust_account_id    = crole.cust_account_id
    AND rel.party_id            = crole.party_id
    WHERE rel.relationship_code = 'CONTACT'
    AND rel.object_table_name   = 'HZ_PARTIES'
    AND rel.subject_table_name  = 'HZ_PARTIES'
    AND rel.subject_type        = 'ORGANIZATION'
    AND rel.object_type         = 'PERSON'
    AND rel.directional_flag    = 'B'
    AND rel.relationship_type   = 'CONTACT'
    AND crole.role_type         = 'CONTACT'
    AND rparty.party_type       = 'PARTY_RELATIONSHIP'
    ORDER BY 1,4,5;

QUERY TO GET LINE LEVEL DETAILS OF SERVICE CONTRACT FORM

SELECT cleb.ID "OLD_CP_LINE_ID",
      OKLB.LINE_NUMBER "LS_LINE_REFERENCE_NUMBER",
      OKHB.CONTRACT_NUMBER "LS_CONTRACT_NUMBER",
      OKHB.CONTRACT_NUMBER_MODIFIER "CONTRACT_NUMBER_MODIFIER",
      CSI.SERIAL_NUMBER "LS_SERIAL_NUMBER",
      CLEB.STS_CODE "LS_STS_CODE",
      MSIB1.SEGMENT1 "LS_ITEM_NUMBER",
      LSET.name "LS_LEVEL" ,
      CLEB.PRICE_NEGOTIATED "LS_PRICE_NEGOTIATED",
      CLEB.START_DATE "LS_START_DATE" ,
      CLEB.END_DATE "LS_END_DATE" ,
      RR1.NAME "ACCT_RULE_CODE",
      OKLT.INVOICE_TEXT "LS_INVOICE_TEXT",
      OKI.NUMBER_OF_ITEMS "LS_QTY",
      OKI.UOM_CODE "LS_UOM",--MSIB.PRIMARY_UOM_CODE "LS_UOM",
      CLEB.PRICE_UNIT "LS_UNIT_PRICE",
      CLEB.PRICE_NEGOTIATED "LS_EXTENDED_PRICE",
      NVL(KLN.TAX_AMOUNT,0.00) "LS_TAX" ,
      CLEB.PRICE_NEGOTIATED "LS_TOTAL",
      OKLB.LINE_RENEWAL_TYPE_CODE "LS_RENEWAL_TYPE_CODE",
      MSIB1.segment1 "LS_SERVICE_PROGRAM_NAME",
      CSI.INSTANCE_NUMBER "LS_INSTANCE_NUMBER" ,
      OKLB.BILL_TO_SITE_USE_ID "LS_BILL_TO_SITE_USE_ID",
      OKLB.SHIP_TO_SITE_USE_ID "LS_SHIP_TO_SITE_USE_ID",
      OKLB.CURRENCY_CODE "LS_CURRENCY_CODE",
      'HOLXIL_UK2_OU' "LS_OPERATING_UNIT",
      'SL' "TABLE_TYPE",
      DECODE(KLN.tax_classification_code,'UKSTD','GB STANDARD','UKEXEMPT', 'GB EXEMPT','UK0','GB ZERO') "LS_TAX_CLASSIFICATION_CODE"
    FROM OKC_K_HEADERS_B OKHB,
      OKS_K_HEADERS_B KHR,
      OKC_K_LINES_B OKLB ,
      OKC_K_LINES_B CLEB,
      OKC_LINE_STYLES_B LSEB,
      OKC_LINE_STYLES_TL LSET ,
      RA_RULES RR1 ,
      OKC_K_LINES_TL OKLTL ,
      OKS_K_LINES_B KLN ,
      OKS_K_LINES_TL OKLT ,
      OKC_K_ITEMS OKI,
      OKC_K_ITEMS OKI1,
      CSI_ITEM_INSTANCES CSI,
      MTL_SYSTEM_ITEMS_B MSIB,
      MTL_SYSTEM_ITEMS_B MSIB1,
      HR_OPERATING_UNITS HOU,
      HZ_CUST_SITE_USES_ALL HCSUA,
      HZ_CUST_SITE_USES_ALL HCSUAS
    WHERE 1     = 1
    AND OKHB.ID = OKLB.CHR_ID
    AND OKHB.ID = KHR.CHR_ID
    AND OKLB.id = CLEB.CLE_ID
      --OR OKHB.ID                      =CLEB.DNZ_CHR_ID
    AND oki1.cle_id                  = oklb.id
    AND MSIB1.INVENTORY_ITEM_ID      = TO_NUMBER(OKI1.OBJECT1_ID1)
    AND msib1.organization_id        = TO_NUMBER(OKI1.OBJECT1_ID2)
    AND LSEB.id                      = CLEB.LSE_ID
    AND RR1.RULE_ID                  = KHR.ACCT_RULE_ID
    AND OKLB.ID                      = KLN.CLE_ID
    AND KLN.ID                       = OKLT.ID
    AND OKLB.ID                      = OKLTL.ID
    AND OKLTL.LANGUAGE               = USERENV('LANG')
    AND OKLT.LANGUAGE                = OKLTL.LANGUAGE
    AND lset.language                = USERENV('LANG')
    AND KLN.ID                       = OKLT.ID
    AND LSEB.ID                      = LSET.ID
    AND OKHB.ID                      = CLEB.DNZ_CHR_ID
    AND OKHB.ID                      = OKI.DNZ_CHR_ID
    AND CLEB.ID                      = OKI.CLE_ID
    AND CSI.LAST_VLD_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    AND TO_NUMBER(OKI.OBJECT1_ID1)   = CSI.INSTANCE_ID
    AND CSI.INVENTORY_ITEM_ID        = MSIB.INVENTORY_ITEM_ID
    AND OKHB.STS_CODE               IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
    AND OKLB.STS_CODE               IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE' , 'HOLD', 'SIGNED')
    AND CLEB.STS_CODE               IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE' , 'HOLD', 'SIGNED')
    AND TRUNC(sysdate) BETWEEN TRUNC(okhb.START_DATE) AND TRUNC(NVL( okhb.END_DATE, sysdate))
    AND TRUNC(sysdate) BETWEEN TRUNC(OKLB.START_DATE) AND TRUNC(NVL( OKLB.END_DATE, sysdate))
    AND TRUNC(sysdate) BETWEEN TRUNC(CLEB.START_DATE) AND TRUNC(NVL( CLEB.END_DATE, sysdate))
    AND okhb.DATE_TERMINATED    IS NULL
    AND okhb.DATETIME_CANCELLED IS NULL
      --AND OKLB.DATE_TERMINATED    IS NULL
      --and OKLB.DATE_CANCELLED     is null
    AND ( HCSUA.SITE_USE_ID  = OKLB.BILL_TO_SITE_USE_ID
    AND hcsua.site_use_code  ='BILL_TO' )
    AND ( HCSUAS.SITE_USE_ID = OKLB.SHIP_TO_SITE_USE_ID
    AND hcsuas.site_use_code ='SHIP_TO' )
    AND HCSUA.ORG_ID         = HOU.ORGANIZATION_ID
    AND HOU.ORGANIZATION_ID  = 'g_uk_org_id'--GIVE YOUR ORGANIZATION NAME TO GET THE DATA FOR THAT ORGANIZATION
    AND (OKHB.CONTRACT_NUMBER = 'p_contract_number' or p_contract_number is NULL) --GIVE YOUR CONTRACT NUMBER HERE
  UNION
  SELECT OKLB.ID "OLD_CP_LINE_ID",
    OKLB.LINE_NUMBER "LS_LINE_REFERENCE_NUMBER",
    OKHB.CONTRACT_NUMBER "LS_CONTRACT_NUMBER",
    OKHB.CONTRACT_NUMBER_MODIFIER "CONTRACT_NUMBER_MODIFIER",
    NULL "LS_SERIAL_NUMBER",
    oklb.STS_CODE "LS_STS_CODE",
    MSIB1.SEGMENT1 "LS_ITEM_NUMBER",
    LSET.name "LS_LEVEL" ,
    oklb.PRICE_NEGOTIATED "LS_PRICE_NEGOTIATED",
    oklb.START_DATE "LS_START_DATE" ,
    oklb.END_DATE "LS_END_DATE" ,
    RR1.NAME "ACCT_RULE_CODE",
    OKLT.INVOICE_TEXT "LS_INVOICE_TEXT",
    NULL "LS_QTY",
    NULL "LS_UOM",--MSIB.PRIMARY_UOM_CODE "LS_UOM",
    NULL "LS_UNIT_PRICE",
    oklb.PRICE_NEGOTIATED "LS_EXTENDED_PRICE",
    NVL(KLN.TAX_AMOUNT,0.00) "LS_TAX" ,
    oklb.PRICE_NEGOTIATED "LS_TOTAL",
    OKLB.LINE_RENEWAL_TYPE_CODE "LS_RENEWAL_TYPE_CODE",
    MSIB1.segment1 "LS_SERVICE_PROGRAM_NAME",
    --CSI.INSTANCE_NUMBER
    NULL "LS_INSTANCE_NUMBER" ,
    OKLB.BILL_TO_SITE_USE_ID "LS_BILL_TO_SITE_USE_ID",
    OKLB.SHIP_TO_SITE_USE_ID "LS_SHIP_TO_SITE_USE_ID",
    OKLB.CURRENCY_CODE "LS_CURRENCY_CODE",
    'HOLXIL_UK2_OU' "LS_OPERATING_UNIT",
    'L' "TABLE_TYPE",
    DECODE(KLN.tax_classification_code,'UKSTD','GB STANDARD','UKEXEMPT', 'GB EXEMPT','UK0','GB ZERO') "LS_TAX_CLASSIFICATION_CODE"
  FROM OKC_K_HEADERS_B OKHB,
    OKS_K_HEADERS_B KHR,
    OKC_K_LINES_B OKLB ,
    --OKC_K_LINES_B CLEB,
    OKC_LINE_STYLES_B LSEB,
    OKC_LINE_STYLES_TL LSET ,
    RA_RULES RR1 ,
    OKC_K_LINES_TL OKLTL ,
    OKS_K_LINES_B KLN ,
    OKS_K_LINES_TL OKLT ,
    --OKC_K_ITEMS OKI,
    OKC_K_ITEMS OKI1,
    --CSI_ITEM_INSTANCES CSI,
    --MTL_SYSTEM_ITEMS_B MSIB,
    MTL_SYSTEM_ITEMS_B MSIB1,
    HR_OPERATING_UNITS HOU,
    HZ_CUST_SITE_USES_ALL HCSUA,
    HZ_CUST_SITE_USES_ALL HCSUAS
  WHERE 1     = 1
  AND OKHB.ID = OKLB.CHR_ID
  AND OKHB.ID = KHR.CHR_ID
    --and OKLB.id                      = CLEB.CLE_ID
    --OR OKHB.ID                      =CLEB.DNZ_CHR_ID
  AND oklb.cle_id            IS NULL
  AND oki1.cle_id             = oklb.id
  AND MSIB1.INVENTORY_ITEM_ID = TO_NUMBER(OKI1.OBJECT1_ID1)
  AND msib1.organization_id   = TO_NUMBER(OKI1.OBJECT1_ID2)
  AND LSEB.id                 = oklb.LSE_ID
  AND RR1.RULE_ID             = KHR.ACCT_RULE_ID
  AND OKLB.ID                 = KLN.CLE_ID
  AND KLN.ID                  = OKLT.ID
  AND OKLB.ID                 = OKLTL.ID
  AND OKLTL.LANGUAGE          = USERENV('LANG')
  AND OKLT.LANGUAGE           = OKLTL.LANGUAGE
  AND lset.language           = USERENV('LANG')
  AND KLN.ID                  = OKLT.ID
  AND LSEB.ID                 = LSET.ID
    --AND OKHB.ID                      = CLEB.DNZ_CHR_ID
    --AND OKHB.ID                      = OKI.DNZ_CHR_ID
    --AND CLEB.ID                      = OKI.CLE_ID
    --AND CSI.LAST_VLD_ORGANIZATION_ID = MSIB.ORGANIZATION_ID
    --AND TO_NUMBER(OKI.OBJECT1_ID1)   = CSI.INSTANCE_ID
    --and CSI.INVENTORY_ITEM_ID        = MSIB.INVENTORY_ITEM_ID
  AND OKHB.STS_CODE IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
  AND OKLB.STS_CODE IN ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
    --and CLEB.STS_CODE in ('BOOKED', 'AGREEMENT', 'AMENDED', 'ENTERED', '
    -- ACTIVE', 'HOLD', 'SIGNED')
  AND TRUNC(sysdate) BETWEEN TRUNC(okhb.START_DATE) AND TRUNC(NVL(okhb.END_DATE , sysdate))
  AND TRUNC(sysdate) BETWEEN TRUNC(OKLB.START_DATE) AND TRUNC(NVL(OKLB.END_DATE , sysdate))
    --and TRUNC(sysdate) between TRUNC(CLEB.START_DATE) and TRUNC(NVL(
    -- CLEB.END_DATE, sysdate))
  AND okhb.DATE_TERMINATED    IS NULL
  AND okhb.DATETIME_CANCELLED IS NULL
  AND OKLB.DATE_TERMINATED    IS NULL
  AND OKLB.DATE_CANCELLED     IS NULL
  AND ( HCSUA.SITE_USE_ID      = OKLB.BILL_TO_SITE_USE_ID
  AND hcsua.site_use_code      ='BILL_TO' )
  AND ( HCSUAS.SITE_USE_ID     = OKLB.SHIP_TO_SITE_USE_ID
  AND hcsuas.site_use_code     ='SHIP_TO' )
  AND HCSUA.ORG_ID             = HOU.ORGANIZATION_ID
  AND HOU.ORGANIZATION_ID      = 'g_uk_org_id'-- GIVE YOUR ORGANIZATION NAME TO GET THE DATA FOR THAT ORGANIZATION
  AND (OKHB.CONTRACT_NUMBER = 'p_contract_number' or p_contract_number is NULL);--GIVE YOUR CONTRACT NUMBER HERE

QUERY TO GET THE HEADER LEVEL FEILDS OF SERVICE CONTRACTS FORM

SELECT chrb.id "CHRB_ID" ,
      chrb.contract_number "CONTRACT_NUMBER" ,
      CHRB.CONTRACT_NUMBER_MODIFIER "LS_MODIFIER_NUMBER" ,
      CHRB.STS_CODE "LS_STS_CODE" ,
      CHRB.START_DATE "START_DATE" ,
      CHRB.END_DATE "END_DATE" ,
      CHRT.SHORT_DESCRIPTION "SHORT_DESCRIPTION" ,
      CHRB.ESTIMATED_AMOUNT "ESTIMATED_AMOUNT" ,
      CHRT.COGNOMEN "KNOWN_AS" ,
      CHRB.AUTHORING_ORG_ID "AUTHORING_ORG_ID" ,
      CHRB.CURRENCY_CODE "CURRENCY_CODE" ,
      RR1.name "ACCT_RULE_CODE" ,
      CHRB.CUST_PO_NUMBER "CUST_PO_NUMBER" ,
      CHRB.CUST_PO_NUMBER_REQ_YN "CUST_PO_NUMBER_REQ_YN" ,
      CHRB.PAYMENT_INSTRUCTION_TYPE "PAYMENT_INSTRUCTION_TYPE" ,
      KHR.SERVICE_PO_NUMBER "PO_NUMBER" ,
      NVL(KHR.AR_INTERFACE_YN,'Y') "AR_INT_FLAG" ,
      CHRB.BILL_TO_SITE_USE_ID "BILL_TO_SITE_USE_ID" ,
      CHRB.SHIP_TO_SITE_USE_ID "SHIP_TO_SITE_USE_ID" ,
      'HOLX_UK2_OU' "LS_OPERATING_UNIT" ,
      (SELECT PRICE_LIST_ID
      FROM OE_PRICE_LISTS
      WHERE UPPER(name) = 'UK2 SUBSIDIARY PRICE LIST (GBP)'--GIVE YOUR PRICE LIST NAME HERE
      ) "PRICE_LIST_ID" ,
    CHRB.INV_RULE_ID "INVOICING_RULE_ID" ,
    CHRB.PAYMENT_TERM_ID "PAYMENT_TERM_ID" ,
    OKP.PDF_ID "APPROVAL_WORKFLOW_ID" ,
    CHRB.QCL_ID "QCL_ID" ,
    CHRB.RENEWAL_TYPE_CODE "RENEWAL_TYPE_CODE" ,
    (SELECT NAME
    FROM RA_CUST_TRX_TYPES_ALL RCTA
    WHERE cust_trx_type_id=KHR.INV_TRX_TYPE
    AND rcta.org_id       ='g_uk_org_id'--GIVE YOUR ORGANIZATION NAME HERE
    ) "TRANSACTION_TYPE_CODE" ,
    KHR.INV_TRX_TYPE "TRANSACTION_TYPE_ID" ,
    (SELECT OKGT.ID
    FROM OKC_K_GRPINGS OKG ,
      OKC_K_GROUPS_TL OKGT
    WHERE OKG.INCLUDED_CHR_ID = CHRB.ID
    AND OKGT.ID               =OKG.CGP_PARENT_ID
    AND OKGT.LANGUAGE         =USERENV('LANG')
    ) "GRP_ID" ,
    chrb.scs_code "SCS_CODE" ,
    okprb.object1_id1 "HDR_PARTY_ID" ,
    (SELECT xsv.SALESREP_ID
    FROM XXHOL_SALESREPS_V xsv
    WHERE upper(ltrim(rtrim(xsv.salesrep_name))) IN
      (SELECT upper(ltrim(rtrim(xsalesrepv.salesrep_name)))
      FROM XXHOL_SALESREPS_V xsalesrepv,
        OKS_K_SALES_CREDITS B
      WHERE B.CHR_ID = CHRB.ID
      AND B.CTC_ID   = xsalesrepv.SALESREP_ID
--      AND B.CLE_ID IS NULL
      )
    AND org_id  = g_uk2_org_id
    AND rownum <= 1
    ) "SALESREP_ID"
    --;select distinct OKLB.*
  FROM okc_k_headers_all_b chrb,
    okc_k_party_roles_b okprb,
    -- OKC_K_LINES_B OKLB ,
    OKS_K_HEADERS_B KHR,
    OKC_K_HEADERS_TL CHRT ,
    OKC_K_PROCESSES OKP ,
    RA_RULES RR1 ,
    HR_OPERATING_UNITS HOU,
    HZ_CUST_SITE_USES_ALL HCSUA,
    HZ_CUST_SITE_USES_ALL HCSUAS
  WHERE 1 =1
    --AND chrb.ID             = OKLB.CHR_ID
  AND CHRB.ID         = CHRT.ID
  AND CHRB.ID         = KHR.CHR_ID
  AND OKP.CHR_ID      =CHRB.id
  AND RR1.RULE_ID     =KHR.ACCT_RULE_ID
  AND okprb.dnz_chr_id=chrb.id
  AND OKPRB.rle_code  ='CUSTOMER'
  AND CHRB.STS_CODE  IN ('QA_HOLD', 'ENTERED', 'ACTIVE', 'HOLD', 'SIGNED')
  AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CHRB.START_DATE,SYSDATE)) AND TRUNC(NVL( CHRB.END_DATE, SYSDATE))
  AND TRUNC(NVL(CHRB.DATE_TERMINATED, SYSDATE))    >= TRUNC(SYSDATE)
  AND TRUNC(NVL(CHRB.DATETIME_CANCELLED, SYSDATE)) >= TRUNC(SYSDATE)
  AND CHRT.LANGUAGE                                 =USERENV('LANG')
  AND ( HCSUA.SITE_USE_ID                           = CHRB.BILL_TO_SITE_USE_ID
  AND hcsua.site_use_code                           ='BILL_TO' )
  AND ( HCSUAS.SITE_USE_ID                          = CHRB.SHIP_TO_SITE_USE_ID
  AND hcsuas.site_use_code                          ='SHIP_TO' )
  AND HCSUA.ORG_ID                                  = HOU.ORGANIZATION_ID
--  AND HOU.ORGANIZATION_ID                           = 'g_org_id'--GIVE YOUR ORGANIZAITON NAME HERE
  AND chrb.org_id          =hou.organization_id
  AND (chrb.CONTRACT_NUMBER = 'p_contract_number');--GIVE YOUR SERVICE CONTRACT NUMBER IN PLACE OF p_contract_number

QUERY FOR AP - XLA - GL RELATION IN EBS R12

SELECT GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  SUM(xla_ae_lines.ENTERED_DR),

  SUM(xla_ae_lines.ENTERED_CR),

  SUM(xla_ae_lines.ACCOUNTED_DR),

  SUM(xla_ae_lines.ACCOUNTED_CR),

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE

FROM xla_ae_headers,

  xla_ae_lines,

  AP_ACCOUNTING_EVENTS_ALL,

  AP_INVOICES_ALL,

  AP_LOOKUP_CODES,

  AP_INVOICE_DISTRIBUTIONS_ALL,

  PO_DISTRIBUTIONS_ALL,

  RCV_TRANSACTIONS RCV_TRANSACTIONS1,

  PO_RELEASES_ALL,

  PO_HEADERS_ALL,

  RCV_SHIPMENT_HEADERS,

  GL_JE_CATEGORIES_VL,

  gl_import_references,

  gl_je_lines,

  GL_JE_HEADERS,

  GL_SETS_OF_BOOKS,

  PO_VENDORS

WHERE 1=1

  --AND xla_ae_headers.je_category_name IN ('Payments','Purchase Invoices','Reconciled Payments'

AND GL_JE_HEADERS.JE_HEADER_ID=173521

AND TRUNC(gl_je_lines.last_update_date) BETWEEN TO_DATE('01-MAY-2014','DD-MON-YYYY')

AND TO_DATE('31-MAY-2014','DD-MON-YYYY')

AND xla_ae_lines.ae_header_id                                                                = xla_ae_headers.ae_header_id

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_TABLE                                                   = 'AP_INVOICES')

AND (AP_ACCOUNTING_EVENTS_ALL.ACCOUNTING_EVENT_ID                                            =xla_ae_headers.EVENT_ID)

AND (AP_ACCOUNTING_EVENTS_ALL.SOURCE_ID                                                      = AP_INVOICES_ALL.INVOICE_ID)

AND (AP_LOOKUP_CODES.LOOKUP_CODE                                                             =AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE)

AND (AP_LOOKUP_CODES.LOOKUP_TYPE                                                             ='INVOICE TYPE')

AND (DECODE(XLA_AE_LINES.SOURCE_TABLE,'AP_INVOICE_DISTRIBUTIONS',XLA_AE_LINES.SOURCE_ID,NULL)=AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID                                         =PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+))

AND (AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID                                         =RCV_TRANSACTIONS1.TRANSACTION_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID                                                      =PO_RELEASES_ALL.PO_RELEASE_ID(+))

AND (PO_DISTRIBUTIONS_ALL.PO_HEADER_ID                                                       =PO_HEADERS_ALL.PO_HEADER_ID(+))

AND (RCV_TRANSACTIONS1.SHIPMENT_HEADER_ID                                                    =RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID(+))

AND (XLA_AE_HEADERS.JE_CATEGORY_NAME                                                         = GL_JE_CATEGORIES_VL.JE_CATEGORY_NAME)

AND (XLA_AE_LINES.GL_SL_LINK_ID                                                              =gl_import_references.GL_SL_LINK_ID)

AND gl_import_references.je_header_id                                                        = gl_je_lines.je_header_id

AND gl_import_references.je_line_num                                                         = gl_je_lines.je_line_num

AND (GL_JE_HEADERS.JE_HEADER_ID                                                              = GL_JE_LINES.JE_HEADER_ID)

AND (GL_JE_LINES.LEDGER_ID                                                                   =GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID)

AND (XLA_AE_LINES.PARTY_ID                                                                   = PO_VENDORS.VENDOR_ID)

GROUP BY GL_JE_HEADERS.JE_HEADER_ID,

  GL_JE_LINES.JE_LINE_NUM,

  xla_ae_headers.ledger_id,

  GL_JE_LINES.PERIOD_NAME,

  GL_SETS_OF_BOOKS.PERIOD_SET_NAME,

  AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,

  xla_ae_lines.SOURCE_ID,

  GL_JE_HEADERS.POSTED_DATE,

  AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,

  AP_LOOKUP_CODES.DISPLAYED_FIELD,

  AP_INVOICES_ALL.INVOICE_NUM,

  AP_INVOICES_ALL.INVOICE_DATE,

  GL_JE_LINES.CODE_COMBINATION_ID,

  GL_JE_HEADERS.CURRENCY_CODE,

  PO_VENDORS.SEGMENT1,

  PO_VENDORS.VENDOR_NAME,

  xla_ae_headers.ACCOUNTING_DATE,

  AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,

  AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,

  PO_HEADERS_ALL.SEGMENT1,

  AP_INVOICES_ALL.INVOICE_NUM,

  PO_RELEASES_ALL.RELEASE_NUM,

  RCV_SHIPMENT_HEADERS.RECEIPT_SOURCE_CODE,

  GL_JE_HEADERS.JE_CATEGORY,

  GL_JE_HEADERS.JE_SOURCE,

  GL_JE_HEADERS.NAME,

  GL_JE_LINES.REFERENCE_1,

  GL_JE_LINES.REFERENCE_3,

  GL_JE_LINES.STATUS,

  GL_JE_LINES.REFERENCE_5,

  GL_JE_LINES.LAST_UPDATE_DATE ;                             

Creating procedure, scenario and scheduling it in ODI

QUERY TO KNOW THE VERSION OF EBS USED

SELECT * FROM FND_PRODUCT_GROUPS;

odi-10182: Uncategorized exception during repository access Error: ora-00001: unique constraint ( pk_txt_header ) violated

Error: ora-00001: unique constraint ( pk_txt_header ) violated

odi-10182: Uncategorized exception during repository access ora-00001: unique constraint ( pk_txt_header) violated

CAUSE:
The table definition was created by duplicating metadata for another table. Indexes are defined as constraints in ODI, when duplicating the original table object the index definitions are duplicated with the original names, for some reason this does not trigger an error at the time of duplicating the tables, the primary key validation for the index name is triggered –in my case- at the time of saving a mapping where the table duplicate is a target.

SOLUTION1:
On Design > Models look up the duplicate table. Once located review and update the names of each of the constraints / indexes defined on metadata and save them. Once you have completed the review you should be able to save the mapping throwing the original issue message.

or

SOLUTION2:
Please check if all the table names used in interface mapping expressions are in interface source tables list/interface joins list because all the tables in mapping expressions are referenced with tables used in interface table source list.

CALENDER OF THE YEAR USING SQL QUERY

SELECT SUBSTR(LPAD( Month, 20-(20-LENGTH(month))/2 ),1,20) Month,
"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') month, TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( month, 'Month YYYY' ), TO_NUMBER(week);

QUICK GUIDE FOR LIST OF UNIX COMMANDS

This quick guide lists commands, including a syntax and brief description. For more detail, use:

$man command

Files and Directories:

These commands allow you to create directories and handle files.

Command	Description
cat	Display File Contents
cd	Changes Directory to dirname
chgrp	change file group
chmod	Changing Permissions
cp	Copy source file into destination
file	Determine file type
find	Find files
grep	Search files for regular expressions.
head	Display first few lines of a file
ln	Create softlink on oldname
ls	Display information about file type.
mkdir	Create a new directory dirname
more	Display data in paginated form.
mv	Move (Rename) a oldname to newname.
pwd	Print current working directory.
rm	Remove (Delete) filename
rmdir	Delete an existing directory provided it is empty.
tail	Prints last few lines in a file.
touch	Update access and modification time of a file.

Manipulating data:
The contents of files can be compared and altered with the following commands.
Command	Description
awk	Pattern scanning and processing language
cmp	Compare the contents of two files
comm	Compare sorted data
cut	Cut out selected fields of each line of a file
diff	Differential file comparator
expand	Expand tabs to spaces
join	Join files on some common field
perl	Data manipulation language
sed	Stream text editor
sort	Sort file data
split	Split file into smaller files
tr	Translate characters
uniq	Report repeated lines in a file
wc	Count words, lines, and characters
vi	Opens vi text editor
vim	Opens vim text editor
fmt	Simple text formatter
spell	Check text for spelling error
ispell	Check text for spelling error
ispell	Check text for spelling error
emacs	GNU project Emacs
ex, edit	Line editor
emacs	GNU project Emacs
emacs	GNU project Emacs
Compressed Files:
Files may be compressed to save space. Compressed files can be created and examined:
Command	Description
compress	Compress files
gunzip	Uncompress gzipped files
gzip	GNU alternative compression method
uncompress	Uncompress files
unzip	List, test and extract compressed files in a ZIP archive
zcat	Cat a compressed file
zcmp	Compare compressed files
zdiff	Compare compressed files
zmore	File perusal filter for crt viewing of compressed text
Getting Information:
Various Unix manuals and documentation are available on-line. The following Shell commands give information:
Command	Description
apropos	Locate commands by keyword lookup
info	Displays command information pages online
man	Displays manual pages online
whatis	Search the whatis database for complete words.
yelp	GNOME help viewer
Network Communication:
These following commands are used to send and receive files from a local UNIX hosts to the remote host around the world.
Command	Description
ftp	File transfer program
rcp	Remote file copy
rlogin	Remote login to a UNIX host
rsh	Remote shell
tftp	Trivial file transfer program
telnet	Make terminal connection to another host
ssh	Secure shell terminal or command connection
scp	Secure shell remote file copy
sftp	secure shell file transfer program
Some of these commands may be restricted at your computer for security reasons.
Messages between Users:
The UNIX systems support on-screen messages to other users and world-wide electronic mail:
Command	Description
evolution	GUI mail handling tool on Linux
mail	Simple send or read mail program
mesg	Permit or deny messages
parcel	Send files to another user
pine	Vdu-based mail utility
talk	Talk to another user
write	Write message to another user
Programming Utilities:
The following programming tools and languages are available based on what you have installed on your Unix.
Command	Description
dbx	Sun debugger
gdb	GNU debugger
make	Maintain program groups and compile programs.
nm	Print program's name list
size	Print program's sizes
strip	Remove symbol table and relocation bits
cb	C program beautifier
cc	ANSI C compiler for Suns SPARC systems
ctrace	C program debugger
gcc	GNU ANSI C Compiler
indent	Indent and format C program source
bc	Interactive arithmetic language processor
gcl	GNU Common Lisp
perl	General purpose language
php	Web page embedded language
py	Python language interpreter
asp	Web page embedded language
CC	C++ compiler for Suns SPARC systems
g++	GNU C++ Compiler
javac	JAVA compiler
appletvieweir	JAVA applet viewer
netbeans	Java integrated development environment on Linux
sqlplus	Run the Oracle SQL interpreter
sqlldr	Run the Oracle SQL data loader
mysql	Run the mysql SQL interpreter
Misc Commands:
These commands list or alter information about the system:
Command	Description
chfn	Change your finger information
chgrp	Change the group ownership of a file
chown	Change owner
date	Print the date
determin	Automatically find terminal type
du	Print amount of disk usage
echo	Echo arguments to the standard options
exit	Quit the system
finger	Print information about logged-in users
groupadd	Create a user group
groups	Show group memberships
homequota	Show quota and file usage
iostat	Report I/O statistics
kill	Send a signal to a process
last	Show last logins of users
logout	log off UNIX
lun	List user names or login ID
netstat	Show network status
passwd	Change user password
passwd	Change your login password
printenv	Display value of a shell variable
ps	Display the status of current processes
ps	Print process status statistics
quota -v	Display disk usage and limits
reset	Reset terminal mode
script	Keep script of terminal session
script	Save the output of a command or process
setenv	Set environment variables
stty	Set terminal options
time	Time a command
top	Display all system processes
tset	Set terminal mode
tty	Print current terminal name
umask	Show the permissions that are given to view files by default
uname	Display name of the current system
uptime	Get the system up time
useradd	Create a user account
users	Print names of logged in users
vmstat	Report virtual memory statistics
w	Show what logged in users are doing
who	List logged in users