TB Accounts Payables Trial Balance - FSSC

with xtb as
  (select /*+ parallel(xtb) leading(xtb) NO_MERGE */
    xtb.definition_code tb_code,
    xtdv.name tb_name,
    nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
    xtb.code_combination_id,
    xtb.source_application_id,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_unrounded_cr,0) - nvl(xtb.entered_unrounded_dr,0))) entered_unrounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0)))   entered_rounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_unrounded_cr,0)   - nvl(xtb.acctd_unrounded_dr,0)))   acctd_unrounded_orig_amount,
    sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0)     - nvl(xtb.acctd_rounded_dr,0)) )    acctd_rounded_orig_amount,
    sum (nvl(xtb.entered_unrounded_cr,0)) - sum(nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
    sum (nvl(xtb.entered_rounded_cr,0))   - sum(nvl(xtb.entered_rounded_dr,0))   entered_rounded_rem_amount,
    sum (nvl(xtb.acctd_unrounded_cr,0))   - sum(nvl(xtb.acctd_unrounded_dr,0))   acctd_unrounded_rem_amount,
    sum (nvl(xtb.acctd_rounded_cr,0))     - sum(nvl(xtb.acctd_rounded_dr,0))     acctd_rounded_rem_amount,
    case when sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0))) = 0
    then 1
    else sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.acctd_rounded_cr,0)     - nvl(xtb.acctd_rounded_dr,0)) ) /
         sum (nvl2(xtb.applied_to_entity_id,0,nvl(xtb.entered_rounded_cr,0)   - nvl(xtb.entered_rounded_dr,0)))
    end conversion_rate,
    xtb.ledger_id,
    xtb.party_id,
    xtb.balancing_segment_value,
    xtb.natural_account_segment_value,
    xtb.cost_center_segment_value,
    xtb.intercompany_segment_value,
    xtb.management_segment_value,
    dte.as_of_date
   from
    xla_trial_balances xtb,
    xla_tb_definitions_vl xtdv,
    (select
      gl.ledger_id,
      to_date(:P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS') as_of_date
     from
      gl_ledgers gl
     where
         :P_AS_OF_DATE is not null
     and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or
           (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and
            exists
             (select 1
              from
               gl_access_sets gas,
               gl_access_set_assignments asa
              where
               gas.access_set_id=asa.access_set_id and
               asa.ledger_id=gl.ledger_id and
               ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
                 gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
               )
             )
           )
         )
     union
     select
      x.ledger_id,
      x.end_date as_of_date
     from
      (select
       1-rank() over (partition by gl.name order by ((glp.period_year * 100000) + glp.period_num) desc) relative_period,
       gl.ledger_id,
       glp.end_date
       from
        gl_ledgers gl
       ,gl_periods glp
       ,gl_periods glpc
       where
           :P_AS_OF_DATE is null
       and :P_RELATIVE_PERIOD is not null
       and ( (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='N') or
             (nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'),'N')='Y' and
              exists
               (select 1
                from
                 gl_access_sets gas,
                 gl_access_set_assignments asa
                where
                 gas.access_set_id=asa.access_set_id and
                 asa.ledger_id=gl.ledger_id and
                 ( gas.access_set_id=nvl(fnd_profile.value('GL_ACCESS_SET_ID'),'-1') or
                   gas.access_set_id=nvl(fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID'),'-1')
                 )
               )
             )
           )
       and gl.period_set_name=glp.period_set_name
       and gl.accounted_period_type=glp.period_type
       and glp.adjustment_period_flag='N'
       and gl.period_set_name=glpc.period_set_name
       and gl.accounted_period_type=glpc.period_type
       and glpc.adjustment_period_flag='N'
       and trunc(sysdate) between glpc.start_date and glpc.end_date
       and glp.start_date <= glpc.start_date
      ) x
     where
         x.relative_period = to_number(:P_RELATIVE_PERIOD)
    ) dte
   where
    xtb.ledger_id = dte.ledger_id and
    xtb.definition_code = xtdv.definition_code and
    xtb.source_application_id=200 and
    xtb.gl_date between to_date('01/01/1950','DD/MM/YYYY') and dte.as_of_date and
    xtdv.enabled_flag='Y'
   group by
    xtb.definition_code,
    xtdv.name,
    nvl(xtb.applied_to_entity_id,xtb.source_entity_id),
    xtb.code_combination_id ,
    xtb.source_application_id,
    xtb.ledger_id,
    xtb.party_id,
    xtb.balancing_segment_value,
    xtb.natural_account_segment_value,
    xtb.cost_center_segment_value,
    xtb.intercompany_segment_value,
    xtb.management_segment_value,
    dte.as_of_date
   having
    sum(nvl(xtb.acctd_rounded_cr,0)) <> sum (nvl(xtb.acctd_rounded_dr,0))
  ),
ap_inv as
(select
  xtb.as_of_date as_of_date,
  xtb.tb_code,
  xtb.tb_name,
  xtb.ledger_id,
  gl.name ledger_name,
  gl.short_name ledger_short_name,
  gl.currency_code ledger_currency,
  haouv.name operating_unit,
  xtb.source_application_id,
  xtb.entity_id source_entity_id,
  xte.entity_code source_entity_code,
  xte.security_id_int_1 org_id,
  xte.transaction_number source_transaction_number,
  xetv.name source_trx_type,
  xtb.code_combination_id,
  xtb.entered_unrounded_orig_amount,
  xtb.entered_rounded_orig_amount,
  xtb.acctd_unrounded_orig_amount,
  xtb.acctd_rounded_orig_amount,
  xtb.entered_unrounded_rem_amount,
  xtb.entered_rounded_rem_amount,
  xtb.acctd_unrounded_rem_amount,
  xtb.acctd_rounded_rem_amount,
  xtb.balancing_segment_value,
  xtb.natural_account_segment_value,
  xtb.cost_center_segment_value,
  xtb.intercompany_segment_value,
  xtb.management_segment_value,
  xtb.entity_id applied_to_entity_id,
  xtb.party_id third_party_number,
  --
  hp.party_name               third_party_name,
  hp.party_id					party_id,
  hps.party_site_name         third_party_site_name,
  ftv2.territory_short_name   third_party_site_country,
  asu.segment1                vendor_number,
  asu.vendor_name             vendor_name,
  asu.vendor_id               vendor_id,
  assa.vendor_site_code       vendor_site,
  ftv1.territory_short_name   vendor_site_country,
  aia.invoice_num             invoice_number,
  aia.doc_sequence_value      invoice_document_number,
  aia.invoice_date            invoice_date,
  aia.gl_date                 invoice_gl_date,
  aia.cancelled_date          invoice_cancelled_date,
  aia.source                  invoice_source,
  xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200)
                              invoice_type,
  aia.description             invoice_description,
  xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id)
                              invoice_status,
  xxen_util.meaning(aia.payment_status_flag,'INVOICE PAYMENT STATUS',200)
                              invoice_payment_status,
  xxen_util.meaning(aia.pay_group_lookup_code,'PAY GROUP',201) pay_group,
  (select ipmv.payment_method_name from iby_payment_methods_vl ipmv where ipmv.payment_method_code = aia.payment_method_code) payment_method,
  aia.dispute_reason          dispute_reason,
  apsa.iby_hold_reason        payment_hold_reason,
  aia.invoice_currency_code   invoice_currency, 
  aia.invoice_amount          invoice_amount,
  nvl(aia.base_amount,aia.invoice_amount) invoice_base_amount,
  case when aia.invoice_currency_code != gl.currency_code then aia.exchange_rate end invoice_exchange_rate,
  case when aia.invoice_currency_code != gl.currency_code then (select gdct.user_conversion_type from gl_daily_conversion_types gdct where gdct.conversion_type = aia.exchange_rate_type) end invoice_exchange_rate_type,
  case when aia.invoice_currency_code != gl.currency_code then aia.exchange_date end invoice_exchange_rate_date,
  CASE WHEN aia.invoice_currency_code != gl.currency_code  THEN    (SELECT CONVERSION_RATE   FROM gl_daily_rates dr
  WHERE dr.FROM_CURRENCY = aia.invoice_currency_code AND dr.TO_CURRENCY = gl.currency_code AND dr.CONVERSION_TYPE = 'Spot'
  AND trunc(dr.CONVERSION_DATE) = trunc( to_date(:P_AS_OF_DATE,'YYYY/MM/DD HH24:MI:SS'))) END closing_exchange_rate,
  aia.invoice_id,
  aia.TOTAL_TAX_AMOUNT,
  apsa.payment_num,
  apsa.due_date,
  apsa.AMOUNT_REMAINING,
  pay.accounting_date payment_date,
ceil(xtb.as_of_date-apsa.due_date)  days_due,
 xtb.acctd_rounded_rem_amount  ps_amount_remaining,
 
  cbbv.bank_name               remit_to_bank_name,
  cbbv.bank_number             remit_to_bank_number,
  cbbv.bank_branch_name        remit_to_branch_name,
  cbbv.branch_number           remit_to_branch_number,
  cbbv.country                 remit_to_branch_country,
  ieba.masked_bank_account_num remit_to_account_num,
  xte.entity_code
 from
  xtb xtb,
  xla_transaction_entities xte,
  xla_entity_types_vl xetv,
  ap_invoices_all aia,
  ap_payment_schedules_all apsa,
  ap_suppliers asu,
  ap_supplier_sites_all assa,
  fnd_territories_vl ftv1,
  hz_parties hp,
  hz_party_sites hps,
  hz_locations hl,
  fnd_territories_vl ftv2,
  gl_ledgers gl,
  fnd_currencies_vl fcv,
  hr_all_organization_units_vl haouv,
  iby_ext_bank_accounts ieba,
  ce_bank_branches_v cbbv,
  ap_invoice_payments_all pay
 where
  xtb.entity_id=xte.entity_id and
  xtb.source_application_id=xte.application_id and
  xte.entity_code='AP_INVOICES' and
  xetv.entity_code=xte.entity_code and
  xetv.application_id=xte.application_id and
  aia.invoice_id=nvl(xte.source_id_int_1,-99) and
  apsa.invoice_id=nvl(xte.source_id_int_1,-99) and
  aia.vendor_id=asu.vendor_id(+) and
  aia.vendor_site_id=assa.vendor_site_id(+) and
  assa.country=ftv1.territory_code(+) and
  aia.party_id=hp.party_id and
  aia.party_site_id=hps.party_site_id(+) and
  (   (asu.employee_id is null and hps.party_site_id is not null)
   or ( asu.employee_id is not null)
  ) and
  hps.location_id=hl.location_id(+) and
  hl.country=ftv2.territory_code(+) and
  xtb.ledger_id=gl.ledger_id and
  gl.currency_code=fcv.currency_code and
  haouv.organization_id=aia.org_id and
  aia.org_id = NVL(:p_org_id , aia.org_id ) and
  apsa.external_bank_account_id = ieba.ext_bank_account_id(+) and
  ieba.branch_id = cbbv.branch_party_id(+) and
  ieba.bank_id = cbbv.bank_party_id(+)
  and aia.invoice_id = pay.invoice_id(+)
and aia.org_id = pay.org_id(+)
)
--
-- Main Query Starts Here
--
select
ap_inv.org_id,
 ap_inv.ledger_name,
 ap_inv.tb_code,
 ap_inv.tb_name trial_balance_name,
 ap_inv.ledger_currency,
 gcck.concatenated_segments account,
 ap_inv.third_party_name,
 ap_inv.third_party_number,
 ap_inv.third_party_site_name,
 ap_inv.third_party_site_country,
 ap_inv.vendor_number,
 ap_inv.vendor_name,
 ap_inv.vendor_site,
 ap_inv.vendor_site_country,
 ap_inv.operating_unit,
 ap_inv.source_trx_type transaction_type,
 ap_inv.source_transaction_number transaction_number,
 ap_inv.invoice_document_number,
 TO_CHAR(ap_inv.invoice_date, 'yyyy/mm/dd') invoice_date,
 ap_inv.invoice_gl_date,
 ap_inv.invoice_cancelled_date,
 ap_inv.invoice_source,
 ap_inv.invoice_type,
 ap_inv.invoice_description,
 ap_inv.invoice_status,
 ap_inv.invoice_payment_status,
 ap_inv.pay_group,
 ap_inv.payment_method,
 ap_inv.dispute_reason,
 ap_inv.payment_hold_reason,
 ap_inv.invoice_currency,
 ap_inv.TOTAL_TAX_AMOUNT,
 ap_inv.invoice_exchange_rate,
 ap_inv.invoice_exchange_rate_type,
 ap_inv.invoice_exchange_rate_date,
  ap_inv.closing_exchange_rate,
 ap_inv.party_id,
 ap_inv.vendor_id,
ap_inv.invoice_amount  invoice_amount,
 ap_inv.invoice_base_amount  invoice_base_amount,
 NVL (ap_inv.invoice_base_amount, ap_inv.invoice_amount) transaction_original_amount,
ap_inv.acctd_rounded_rem_amount  trans_remaining_amount,
 ap_inv.payment_num,
 ap_inv.as_of_date,
  Case
       When ap_inv.invoice_exchange_rate > 0
	    then ap_inv.AMOUNT_REMAINING
       else null
   end transaction_remaining_amount,
 
 
   (ap_inv.invoice_amount * ap_inv.invoice_exchange_rate) AMOUNT_IN_SOURCE_CUR,
 Case
       When ap_inv.invoice_exchange_rate > 0
	    then (NVL (ap_inv.invoice_base_amount, ap_inv.invoice_amount) ) /ap_inv.invoice_exchange_rate
       else null
end as Remaining_amount_source,
CASE
           WHEN ap_inv.TOTAL_TAX_AMOUNT <> 0
           THEN
                 (CASE
                    WHEN TRUNC(invoice_date) <=
                         TO_DATE ('31-MAR-2018', 'dd-mon-yyyy')
                       THEN
                             ap_inv.acctd_rounded_rem_amount
                        * (14 / 114) 
                        ELSE
                             ap_inv.acctd_rounded_rem_amount
                        * (15 / 115) 
                   END)
 
       END    AS VAT_amt_incl_remaining_amt, 
 
 TO_CHAR(ap_inv.due_date, 'yyyy/mm/dd') due_date,
 ap_inv.days_due,
 case when ceil(ap_inv.as_of_date-ap_inv.due_date) between -9999 and 30 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end cur,
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 31 and 60 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_30",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 61 and 90 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_60",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 91 and 120 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_120",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 121 and 180 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_180",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 181 and 270 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_270",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 271 and 365 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Days_365",
case when ceil(ap_inv.as_of_date-ap_inv.due_date) between 366 and 99999 and ap_inv.ps_amount_remaining != 0 then ap_inv.ps_amount_remaining end "Plus_365",
 ap_inv.balancing_segment_value balancing_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_BALANCING',ap_inv.balancing_segment_value) balancing_segment_desc,
 ap_inv.natural_account_segment_value account_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'GL_ACCOUNT',ap_inv.natural_account_segment_value) account_segment_desc,
 ap_inv.cost_center_segment_value cost_center_segment,
 gl_flexfields_pkg.get_description(gcck.chart_of_accounts_id,'FA_COST_CTR',ap_inv.cost_center_segment_value) cost_center_segment_desc,
     gcck.segment1                         Business_Unit,
       gcck.segment2                         Site,
       gcck.segment3                         Cost_Center,
       gcck.segment4                         Main_Code,
       gcck.segment5                         Account1,
       gcck.segment6                         Product_Group,
 ap_inv.remit_to_bank_name,
 ap_inv.remit_to_bank_number,
 ap_inv.remit_to_branch_name,
 ap_inv.remit_to_branch_number,
 ap_inv.remit_to_branch_country,
 ap_inv.remit_to_account_num,
 --
 ap_inv.invoice_id,
 ap_inv.entity_code,
 TO_CHAR(ap_inv.payment_date, 'yyyy/mm/dd') payment_date
 from
 ap_inv ap_inv,
 gl_code_combinations_kfv gcck
where ap_inv.tb_code =:P_DEFINITION_CODE
 and
 ap_inv.org_id= nvl(:P_SECURITY_ID_INT_1,ap_inv.org_id)
and
1=1 and
 gcck.code_combination_id (+) = ap_inv.code_combination_id and
 (ap_inv.vendor_id= nvl(SUBSTR(:P_THIRD_PARTY_ID,1,INSTR(:P_THIRD_PARTY_ID, '#') - 1) , ap_inv.vendor_id)
 or ap_inv.vendor_id= nvl(SUBSTR(:P_THIRD_PARTY_ID,1,INSTR(:P_THIRD_PARTY_ID, '#') - 1), ap_inv.vendor_id )) AND
 nvl(ap_inv.ps_amount_remaining,0) != 0
 
 --and source_transaction_number ='29090999'
 
order by
 ap_inv.ledger_name,
 ap_inv.tb_name,
 gcck.concatenated_segments,
 ap_inv.third_party_name,
 ap_inv.third_party_number,
 ap_inv.invoice_gl_date,
 ap_inv.invoice_id,
 ap_inv.payment_num