Performance Obligation report

avatar
unknown
sql
2 years ago
10 kB
120
Indexable
SELECT
revenue_contract,
performance_obligation,
ledger_id,
ledger_name,
(CASE WHEN COUNT(DISTINCT customer_name) > 1
       THEN 'MULTIPLE'
       ELSE MIN(customer_name)
       END) customer_name,
(CASE WHEN COUNT(DISTINCT customer_account_number) > 1
       THEN 'MULTIPLE'
       ELSE MIN(customer_account_number)
       END) customer_account_number,
(CASE WHEN COUNT(DISTINCT legal_entity_name) > 1
       THEN 'MULTIPLE'
       ELSE MIN(legal_entity_name)
       END) legal_entity_name,
(CASE WHEN COUNT(DISTINCT item_memo_line) > 1
       THEN 'MULTIPLE'
       ELSE MIN(item_memo_line)
  END) item_memo_line,
  (CASE WHEN COUNT(DISTINCT source_doc_reference) > 1
       THEN 'MULTIPLE'
       ELSE MIN(source_doc_reference)
  END) source_doc_reference,
   period_name,
   effective_period_num,
   account_class,
   gl_account,
   currency_code,
   SUM(account_balance) account_balance_sum FROM (SELECT
        vcch.customer_contract_number   revenue_contract
       ,vpo.perf_obligation_number      performance_obligation
       ,hp.party_name                   customer_name
       ,hca.account_number              customer_account_number
       ,xal.ledger_id                   ledger_id
       ,gll.name                        ledger_name
       ,xep.name                        legal_entity_name
       ,NVL(esi.item_number,arml.name )  item_memo_line
       ,(vsdt.name||'.'||vsd.DOCUMENT_NUMBER||'.'||vsdl.LINE_NUM) source_doc_reference
       ,glp.period_name                 period_name
	   ,glp.effective_period_num        effective_period_num
       ,xlal.meaning                    account_class
,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',gll.CHART_OF_ACCOUNTS_ID,NULL,xal.code_combination_id,'GL_ACCOUNT','Y','VALUE') GL_ACCOUNT
       ,xal.currency_code               currency_code
/*       ,(nvl(accounted_dr,0) - nvl(accounted_cr,0)) account_balance*/
       ,(nvl(xdl.unrounded_accounted_dr,0) - nvl(xdl.unrounded_accounted_cr,0)) account_balance
  FROM
        VRM_CUSTOMER_CONTRACT_HEADERS   vcch,
        VRM_PERF_OBLIGATIONS            vpo,
        VRM_PERF_OBLIGATION_LINES       vpol,
        HZ_PARTIES                      hp,
        HZ_CUST_ACCOUNTS                hca,
        GL_LEDGERS                      gll,
        VRM_SOURCE_DOCUMENTS            vsd,
        VRM_SOURCE_DOC_LINES            vsdl,
        XLE_ENTITY_PROFILES             xep,
        VRM_SOURCE_DOC_TYPES_VL         vsdt,
        VRM_PERF_OBLIGATION_LIN_DISTS   vpold,
        XLA_AE_LINES                    xal,
        XLA_DISTRIBUTION_LINKS          xdl,
        XLA_AE_HEADERS                  xah,
        GL_PERIOD_STATUSES              glp,
        AR_MEMO_LINES_ALL_VL            arml,
        EGP_SYSTEM_ITEMS_VL              esi,
        XLA_LOOKUPS                     xlal
  WHERE
        vpo.customer_contract_header_id = vcch.customer_contract_header_id
   AND  vpo.perf_obligation_id = vpol.perf_obligation_id
   AND  vpold.PERF_OBLIGATION_LINE_ID = vpol.PERF_OBLIGATION_LINE_ID
   AND  xdl.source_distribution_id_num_1 = vpold.perf_obligation_line_dist_id
   AND  xdl.application_id = vpold.application_id
   AND  xdl.source_distribution_type = vpold.account_class
   AND  vsd.bill_to_customer_id = hca.cust_account_id
   AND  vsd.legal_entity_id = xep.legal_entity_id
   AND  vsd.document_id = vsdl.document_id
   AND  hca.party_id = hp.party_id
   AND  vsdl.document_line_id = vpol.document_line_id
   AND  vsdl.DOCUMENT_TYPE_ID = vsdt.DOCUMENT_TYPE_ID
   AND  vsdl.memo_line_seq_id = arml.memo_line_seq_id (+)
   AND  vsdl.item_id = esi.inventory_item_id (+)
   AND  vsdl.inventory_org_id = esi.inventory_organization_id (+)
   AND  xal.ae_header_id = xdl.ae_header_id
   AND  xal.ae_line_num = xdl.ae_line_num
   AND  xal.ae_header_id = xah.ae_header_id
   AND  xah.ledger_id = :P_LedgerId
   AND  gll.ledger_id = xah.ledger_id
   AND  glp.ledger_id = xah.ledger_id
   AND  glp.application_id = 10455
   AND  xah.accounting_date between glp.start_date and glp.end_date
   AND  glp.adjustment_period_flag='N'
   AND  glp.effective_period_num between :P_StartPeriodNum and :P_EndPeriodNum
   AND  xal.accounting_class_code = NVL(:P_AccountClass, xal.accounting_class_code)
   AND  xal.accounting_class_code IN ('ORA_CONTRACT_ASSET','ORA_CONTRACT_LIABILITY','ORA_CONTRACT_CLEARING','ORA_CONTRACT_PRICE_VARIANCE','ORA_CONTRACT_REVENUE')
   AND  xlal.lookup_type = 'XLA_ACCOUNTING_CLASS'
   AND  xlal.lookup_code = xal.accounting_class_code
   UNION ALL 
   SELECT
        vcch.customer_contract_number   revenue_contract
       ,NULL                            performance_obligation
       ,NULL                            customer_name
       ,NULL                            customer_account_number
       ,xal.ledger_id                   ledger_id
       ,gll.name                        ledger_name
       ,NULL                            legal_entity_name
       ,NULL                            item_memo_line
       ,NULL                            source_doc_reference
       ,glp.period_name                 period_name
	   ,glp.effective_period_num        effective_period_num
       ,xlal.meaning                    account_class
,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',gll.CHART_OF_ACCOUNTS_ID,NULL,xal.code_combination_id,'GL_ACCOUNT','Y','VALUE') GL_ACCOUNT
       ,xal.currency_code               currency_code
/*       ,(nvl(accounted_dr,0) - nvl(accounted_cr,0)) account_balance*/
       ,(nvl(xdl.unrounded_accounted_dr,0) - nvl(xdl.unrounded_accounted_cr,0)) account_balance
   FROM
        VRM_CUSTOMER_CONTRACT_HEADERS   vcch,
        VRM_PERF_OBLIGATIONS            vpo,
        VRM_PERF_OBLIGATION_LINES       vpol,
        GL_LEDGERS                      gll,
        VRM_PERF_OBLIGATION_LIN_DISTS   vpold,
        XLA_AE_LINES                    xal,
        XLA_DISTRIBUTION_LINKS          xdl,
        XLA_AE_HEADERS                  xah,
        GL_PERIOD_STATUSES              glp,
        XLA_LOOKUPS                     xlal
    WHERE
        vpo.customer_contract_header_id = vcch.customer_contract_header_id
   AND  vpo.perf_obligation_id = vpol.perf_obligation_id
   AND  vpold.PERF_OBLIGATION_LINE_ID = vpol.PERF_OBLIGATION_LINE_ID
   AND  xdl.source_distribution_id_num_1 = vpold.perf_obligation_line_dist_id
   AND  xdl.application_id = vpold.application_id
   AND  xdl.source_distribution_type = vpold.account_class
   AND  xal.ae_header_id = xdl.ae_header_id
   AND  xal.ae_line_num = xdl.ae_line_num
   AND  xal.ae_header_id = xah.ae_header_id
   AND  xah.ledger_id = :P_LedgerId
   AND  gll.ledger_id = xah.ledger_id
   AND  glp.ledger_id = xah.ledger_id
   AND  glp.application_id = 10455
   AND  xah.accounting_date between glp.start_date and glp.end_date
   AND  glp.adjustment_period_flag='N'
   AND  glp.effective_period_num between :P_StartPeriodNum and :P_EndPeriodNum
   AND  xal.accounting_class_code = NVL(:P_AccountClass, xal.accounting_class_code)
   AND  xal.accounting_class_code IN ('ORA_CONTRACT_DISCOUNT')
   AND  xlal.lookup_type = 'XLA_ACCOUNTING_CLASS'
   AND  xlal.lookup_code = xal.accounting_class_code
   UNION ALL /* For Initial Performance Event*/
   SELECT
        vcch.customer_contract_number   revenue_contract
       ,TO_NUMBER(DECODE(vpold.perf_obligation_id, -9999, NULL, vpold.perf_obligation_id))      performance_obligation /* CRAB */ 
       ,NULL                            customer_name
       ,NULL                            customer_account_number
       ,xal.ledger_id                   ledger_id
       ,gll.name                        ledger_name
       ,NULL                            legal_entity_name
       ,NULL                            item_memo_line
       ,NULL                            source_doc_reference
       ,glp.period_name                 period_name
       ,glp.effective_period_num        effective_period_num
       ,xlal.meaning                    account_class
,FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1('FLEXFIELD','GL','GL#',gll.CHART_OF_ACCOUNTS_ID,NULL,xal.code_combination_id,'GL_ACCOUNT','Y','VALUE') GL_ACCOUNT
       ,xal.currency_code               currency_code
/*       ,(nvl(accounted_dr,0) - nvl(accounted_cr,0)) account_balance*/
       ,(nvl(xdl.unrounded_accounted_dr,0) - nvl(xdl.unrounded_accounted_cr,0)) account_balance
   FROM
        VRM_CUSTOMER_CONTRACT_HEADERS   vcch,
        --VRM_PERF_OBLIGATIONS            vpo, /* CRAB */       
        GL_LEDGERS                      gll,
        VRM_PERF_OBLIGATION_LIN_DISTS   vpold,
        XLA_AE_LINES                    xal,
        XLA_DISTRIBUTION_LINKS          xdl,
        XLA_AE_HEADERS                  xah,
        GL_PERIOD_STATUSES              glp,
        XLA_LOOKUPS                     xlal
        /* CRAB */
        /* WHERE
        vpo.customer_contract_header_id = vcch.customer_contract_header_id 
         AND  vpold.PERF_OBLIGATION_ID = vpo.PERF_OBLIGATION_ID    */
   WHERE vpold.customer_contract_header_id = vcch.customer_contract_header_id   
   AND  vpold.perf_obligation_line_id IS NULL 
   AND  xdl.source_distribution_id_num_1 = vpold.perf_obligation_line_dist_id
   AND  xdl.application_id = vpold.application_id
   AND  xdl.source_distribution_type = vpold.account_class
   AND  xal.ae_header_id = xdl.ae_header_id
   AND  xal.ae_line_num = xdl.ae_line_num
   AND  xal.ae_header_id = xah.ae_header_id
   AND  xah.ledger_id = :P_LedgerId
   AND  gll.ledger_id = xah.ledger_id
   AND  glp.ledger_id = xah.ledger_id
   AND  glp.application_id = 10455
   AND  xah.accounting_date between glp.start_date and glp.end_date
   AND  glp.adjustment_period_flag='N'
   AND  glp.effective_period_num between :P_StartPeriodNum and :P_EndPeriodNum
   AND  xal.accounting_class_code = NVL(:P_AccountClass, xal.accounting_class_code)
   AND  xal.accounting_class_code IN ('ORA_CONTRACT_ASSET','ORA_CONTRACT_LIABILITY', 'WRITE_OFF','ORA_CONTRACT_DISCOUNT') /* CRAB */
   AND  xlal.lookup_type = 'XLA_ACCOUNTING_CLASS'
   AND  xlal.lookup_code = xal.accounting_class_code
   )
  GROUP BY
   revenue_contract,
   performance_obligation,
   ledger_id,
   ledger_name,
   customer_name,
   customer_account_number,
   legal_entity_name,
   item_memo_line,
   source_doc_reference,
   period_name,
   effective_period_num,
   account_class,
   gl_account,
   currency_code
   ORDER BY 
   effective_period_num,
   revenue_contract,
   performance_obligation
Editor is loading...
Leave a Comment