Search This Blog

Thursday, April 4, 2013

XLA Links to Other Modules


CREATE OR REPLACE PROCEDURE APPS.XX_GL_MV_DRILL_REPORT (errbuf VARCHAR2, retcode NUMBER)
AS
--START_DATE DATE;
CURSOR LEG_ID IS (SELECT '2027' LEDGER_ID FROM DUAL
                  UNION
                  SELECT '2062' LEDGER_ID FROM DUAL);

/*SELECT GSB.LEDGER_ID
FROM GL_LEDGERS GSB
WHERE EXISTS(SELECT 1 FROM GL_BALANCES GB WHERE GB.LEDGER_ID=GSB.LEDGER_ID));
*/
BEGIN

   -----------------*********DELETE******--------------
         EXECUTE IMMEDIATE 'TRUNCATE TABLE XX_GL_MV_REPORT';
         COMMIT;

   -----------------*********FLAG 1******--------------




FOR GL_LED_ID IN LEG_ID
LOOP
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '1' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, acr.doc_sequence_value, amcd.gl_date,
                   acr.receipt_number trx_number, acr.cash_receipt_id,
                   rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, acr.receipt_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   NVL (acr.comments, acr.reversal_comments) narration,
                   rc.customer_id, rc.customer_number, hcsu.site_use_id,
                   hcsu.site_use_code, acr.status check_status,
                   acr.reversal_date check_void_date, cc.account_type,
                   NULL reference1, jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ar_distributions_all ada,
                   ar_misc_cash_distributions_all amcd,
                   ar_cash_receipts_all acr,
                   hr_operating_units hou,
                   fnd_user fu,
                   hz_cust_site_uses_all hcsu,
                   ar_customers rc,
                   DUAL
             WHERE 1 = 1
               AND xlajed.source_distribution_id_num_1 = ada.line_id
               AND ada.source_table = 'MCD'
               AND ada.source_id = amcd.misc_cash_distribution_id
               AND amcd.cash_receipt_id = acr.cash_receipt_id
               AND acr.org_id = hou.organization_id
               AND acr.last_updated_by = fu.user_id(+)
               AND acr.pay_from_customer = rc.customer_id(+)
               AND acr.customer_site_use_id = hcsu.site_use_id(+)
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               AND jeh.je_category = 'Misc Receipts'
               AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------
               --AND JEL.EFFECTIVE_DATE BETWEEN AND SYSDATE



);

fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 1');

   -----------------*********FLAG 2******--------------
           INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '2' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, NULL org_name,
                   NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
                   adj.doc_sequence_value, adj.gl_date,
                   rct.trx_number trx_number, rct.customer_trx_id,
                   rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, jel.effective_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   adj.comments narration, rc.customer_id, rc.customer_number,
                   hcsu.site_use_id, hcsu.site_use_code, NULL check_status,
                   NULL check_void_date, cc.account_type, NULL reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ar_distributions_all ada,
                   ar_adjustments_all adj,
                   ra_customer_trx_all rct,
                   hz_cust_site_uses_all hcsu,
                   hr_operating_units hou,
                   ar_customers rc,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND xlajed.source_distribution_id_num_1 = ada.line_id
               AND ada.source_table = 'ADJ'
               AND ada.source_id = adj.adjustment_id
               AND adj.org_id = hou.organization_id
               AND adj.last_updated_by = fu.user_id(+)
               AND rct.customer_trx_id = adj.customer_trx_id
               AND rct.bill_to_customer_id = rc.customer_id(+)
               AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               AND jeh.je_category = 'Adjustment'
               AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------


);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID'||GL_LED_ID.LEDGER_ID||'---'||'FLAG 2');

  -----------------*********FLAG 4******--------------
         INSERT INTO XX_GL_MV_REPORT
         (SELECT flag, code_combination_id, SOURCE, set_of_books_id, sob, CATEGORY,
          period, batch_name, header_name, description, currency,
          sob_currency, entered_debit, entered_credit, accounted_debit,
          accounted_credit, org_name, user_name, gl_voucher_number,
          posted_date, sl_voucher_number, gl_date, trx_number,
          cash_receipt_id, cust_supp_name, effective_date, receipt_date,
          gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7,
          gl_seg8, gl_seg9, gl_seg10, gl_seg11, gl_seg12, gl_name_seg1,
          gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5,
          gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9,
          gl_name_seg10, gl_name_seg11, gl_name_seg12, exp_emp_no, project_no,
          trx_batch_name, status, narration, customer_id, customer_number,
          site_use_id, site_use_code, check_status, check_void_date,
          account_type, reference1, creation_date, je_header_id,
          accrual_rev_je_header_id, accrual_rev_period_name, dummy,
          payment_status, invoice_type_code, je_line_num, gl_fbt, ap_fbt
         FROM (SELECT '4' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id set_of_books_id, gsb.NAME sob,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code currency, gsb.currency_code sob_currency,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credit, hou.NAME org_name,
                  INITCAP (fu.description) user_name,
                  jeh.doc_sequence_value gl_voucher_number, jeh.posted_date,
                  acr.doc_sequence_value sl_voucher_number,
                  jeh.posted_date gl_date,
                                          --acrh.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL dummy, NULL payment_status, NULL invoice_type_code,
                  jel.je_line_num, jel.attribute3 gl_fbt, NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_cash_receipt_history_all acrh,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND jeh.je_header_id = 1825109
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'CRH'
              --AND ada.source_type = 'CASH'
              AND ada.source_type IN ('CASH', 'BANK_CHARGES')
              AND ada.source_id = acrh.cash_receipt_history_id
              AND acrh.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN
                --       ('Receipts', 'Credit Memos', 'Misc Receipts')
              AND jeh.je_category IN
                     ('Receipts',
                      'Credit Memos',
                      'Misc Receipts',
                      'Debit Memos'
                     )
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND acrh.org_id = hou.organization_id
              --AND acrh.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
           UNION
           SELECT '4' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date, acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                                          --ara.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND jeh.je_header_id = 1825109
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'RA'
              --AND ada.source_type NOT IN ('UNID')
              AND ada.source_id = ara.receivable_application_id
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN ('Receipts', 'Credit Memos')
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              --AND ara.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
--AND cc.gl_seg2 != '417001'
           UNION
           SELECT DISTINCT '4' flag, jel.code_combination_id,
                           NVL (src.user_je_source_name, '**********') SOURCE,
                           gsb.ledger_id, gsb.NAME,
                           NVL (cat.user_je_category_name,
                                '**********'
                               ) CATEGORY,
                           jel.period_name period_name, jeb.NAME batch_name,
                           jeh.NAME header_name, jel.description description,
                           jeh.currency_code,
                           gsb.currency_code sob_currency_code,
                           xlajel.entered_dr entered_debit,
                           xlajel.entered_cr entered_credit,
                           xlajel.accounted_dr accounted_debit,
                           xlajel.accounted_cr accounted_credits,
                           hou.NAME org_name,
                           INITCAP (fu.description) user_name,
                           jeh.doc_sequence_value, jeh.posted_date,
                           rct.doc_sequence_value, jeh.posted_date gl_date,
                           rct.trx_number trx_number, rct.customer_trx_id,
                           rc.customer_name cust_supp_name,
                           xlajel.accounting_date effective_date,
                           rct.trx_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3,
                           cc.gl_seg4, cc.gl_seg5, cc.gl_seg6, cc.gl_seg7,
                           cc.gl_seg8, cc.gl_seg9, cc.gl_seg10, cc.gl_seg11,
                           cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                           cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                           cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                           cc.gl_name_seg9, cc.gl_name_seg10,
                           cc.gl_name_seg11, cc.gl_name_seg12,
                           NULL exp_emp_no, NULL project_no,
                           NULL trx_batch_name, jeh.status,
                           rct.comments narration, rc.customer_id,
                           rc.customer_number, hcsu.site_use_id,
                           hcsu.site_use_code, rct.status_trx check_status,
                           NULL check_void_date, cc.account_type,
                           NULL reference1, jel.creation_date,
                           jeh.je_header_id, jeh.accrual_rev_je_header_id,
                           jeh.accrual_rev_period_name, NULL, NULL, NULL,
                           jel.je_line_num, jel.attribute3 gl_fbt,
                           NULL ap_fbt
                      FROM disc_ccid_dsc_mv cc,
                           gl_ledger_names_v gsb,
                           gl_je_lines jel,
                           gl_je_headers jeh,
                           gl_import_references gir,
                           gl_je_batches jeb,
                           gl_je_categories cat,
                           gl_je_sources src,
                           xla_ae_lines xlajel,
                           xla_distribution_links xlajed,
                           ar_distributions_all ada,
                           ar_receivable_applications_all ara,
                           ra_customer_trx_all rct,
                           hr_operating_units hou,
                           fnd_user fu,
                           hz_cust_site_uses_all hcsu,
                           ar_customers rc,
                           DUAL
                     WHERE 1 = 1
                       --AND jeh.je_header_id = 1825109
                       AND xlajel.ae_header_id = xlajed.ae_header_id
                       AND xlajel.ae_line_num = xlajed.ae_line_num
                       AND xlajed.source_distribution_id_num_1 = ada.line_id
                       AND ada.source_table = 'RA'
                       AND ada.source_id = ara.receivable_application_id
                       AND ara.customer_trx_id = rct.customer_trx_id
                       AND gir.je_header_id = jeh.je_header_id
                       AND gir.je_line_num = jel.je_line_num
                       AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
                       AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
                       AND jel.code_combination_id = cc.code_combination_id
                       AND jel.status || '' = 'P'
                       AND (   xlajel.accounted_dr != 0
                            OR xlajel.accounted_cr != 0
                           )
                       AND jeh.je_header_id = jel.je_header_id
                       AND jeh.actual_flag = 'A'
                       AND jeb.je_batch_id = jeh.je_batch_id
                       AND jeh.ledger_id = gsb.ledger_id
                       AND jeb.average_journal_flag = 'N'
                       AND src.je_source_name = jeh.je_source
                       AND cat.je_category_name = jeh.je_category
                       AND jeh.je_source = 'Receivables'
                       AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
                       AND xlajed.source_distribution_type =
                                                        'AR_DISTRIBUTIONS_ALL'
                       AND ara.org_id = hou.organization_id
                       AND jeh.last_updated_by = fu.user_id(+)
                       AND rct.bill_to_customer_id = rc.customer_id(+)
                       AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
           UNION ALL
           SELECT DISTINCT '4' flag, jel.code_combination_id,
                           NVL (src.user_je_source_name, '**********') SOURCE,
                           gsb.ledger_id set_of_books_id, gsb.NAME sob,
                           NVL (cat.user_je_category_name,
                                '**********'
                               ) CATEGORY,
                           jel.period_name period, jeb.NAME batch_name,
                           jeh.NAME header_name, jel.description description,
                           jeh.currency_code currency,
                           gsb.currency_code sob_currency,
                           xlajel.entered_dr entered_debit,
                           xlajel.entered_cr entered_credit,
                           xlajel.accounted_dr accounted_debit,
                           xlajel.accounted_cr accounted_credit,
                           NULL org_name, INITCAP (fu.description) user_name,
                           jeh.doc_sequence_value gl_voucher_number,
                           jeh.posted_date, NULL sl_voucher_number,
                           jeh.posted_date gl_date, NULL trx_number,
                           NULL cash_receipt_id, NULL cust_supp_name,
                           xlajel.accounting_date effective_date,
                           NULL receipt_date, cc.gl_seg1, cc.gl_seg2,
                           cc.gl_seg3, cc.gl_seg4, cc.gl_seg5, cc.gl_seg6,
                           cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                           cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                           cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                           cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                           cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                           cc.gl_name_seg11, cc.gl_name_seg12,
                           NULL exp_emp_no, NULL project_no,
                           NULL trx_batch_name, jeh.status, NULL narration,
                           NULL customer_id, NULL customer_number,
                           NULL site_use_id, NULL site_use_code,
                           NULL check_status, NULL check_void_date,
                           cc.account_type, NULL reference1,
                           jel.creation_date, jeh.je_header_id,
                           jeh.accrual_rev_je_header_id,
                           jeh.accrual_rev_period_name, NULL dummy,
                           NULL payment_status, NULL invoice_type_code,
                           jel.je_line_num, jel.attribute3 gl_fbt,
                           NULL ap_fbt
                      FROM disc_ccid_dsc_mv cc,
                           gl_ledger_names_v gsb,
                           gl_je_lines jel,
                           gl_je_headers jeh,
                           gl_import_references gir,
                           gl_je_batches jeb,
                           gl_je_categories cat,
                           gl_je_sources src,
                           xla_ae_lines xlajel,
                           xla_distribution_links xlajed,
                           fnd_user fu,
                           DUAL
                     WHERE 1 = 1
                       AND xlajel.ae_header_id = xlajed.ae_header_id
                       AND xlajel.ae_line_num = xlajed.ae_line_num
                       AND gir.je_header_id = jeh.je_header_id
                       AND gir.je_line_num = jel.je_line_num
                       AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
                       AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
                       AND jel.code_combination_id = cc.code_combination_id
                       AND jel.status || '' = 'P'
                       AND (   xlajel.accounted_dr != 0
                            OR xlajel.accounted_cr != 0
                           )
                       AND jeh.je_header_id = jel.je_header_id
                       AND jeh.actual_flag = 'A'
                       AND jeb.je_batch_id = jeh.je_batch_id
                       AND jeh.ledger_id = gsb.ledger_id
                       AND jeb.average_journal_flag = 'N'
                       AND src.je_source_name = jeh.je_source
                       AND cat.je_category_name = jeh.je_category
                       AND jeh.je_source = 'Receivables'
                       AND jeh.je_category IN
                              ('Receipts',
                               'Credit Memos',
                               'Misc Receipts',
                               'Debit Memos'
                              )
                       AND xlajed.source_distribution_type = 'XLA_MANUAL'
                       AND jeh.last_updated_by = fu.user_id(+))
                       WHERE set_of_books_id=GL_LED_ID.LEDGER_ID-------------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 4');
  -----------------*********FLAG 6******--------------
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '6' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, NULL org_name,
                   NULL user_name, jeh.doc_sequence_value, jeh.posted_date,
                   NULL, rctlgd.gl_date, rct.trx_number trx_number,
                   rct.customer_trx_id, rc.customer_name cust_supp_name,
                   xlajel.accounting_date effective_date, jel.effective_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   rel_acctg_pkg.get_ar_invoice_detail
                                               (jel.ledger_id,
                                                rct.customer_trx_id
                                               ) narration,
                   rc.customer_id, rc.customer_number, hcsu.site_use_id,
                   hcsu.site_use_code, NULL check_status,
                   NULL check_void_date, cc.account_type, NULL reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_distribution_links xlajed,
                   ra_cust_trx_line_gl_dist_all rctlgd,
                   ra_customer_trx_all rct,
                   hz_cust_site_uses_all hcsu,
                   ar_customers rc,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajed.source_distribution_id_num_1 =
                                               rctlgd.cust_trx_line_gl_dist_id
               AND rctlgd.customer_trx_id = rct.customer_trx_id
               AND rct.bill_to_customer_id = rc.customer_id(+)
               AND rct.bill_to_site_use_id = hcsu.site_use_id(+)
               AND rctlgd.last_updated_by = fu.user_id(+)
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Receivables'
               --AND jeh.je_category IN
               --             ('Sales Invoices', 'Chargebacks', 'Credit Memos')
               AND jeh.je_category IN
                            ('Sales Invoices', 'Chargebacks', 'Credit Memos', 'Debit Memos')
               AND xlajed.source_distribution_type =
                                                'RA_CUST_TRX_LINE_GL_DIST_ALL'
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 6');
  -----------------*********FLAG 10******--------------
         INSERT INTO XX_GL_MV_REPORT
                   (SELECT DISTINCT '10' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
                   ai.invoice_num trx_number, ai.invoice_id,
                   pov.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ai.invoice_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   ai.description narration, pov.vendor_id,
                   pov.segment1 vendor_number, povs.vendor_site_id,
                   povs.vendor_site_code, NULL check_status,
                   NULL check_void_date, cc.account_type,
                   DECODE (ai.attribute_category,
                           'RL Invoice Header', ai.attribute10,
                           NULL
                          ) reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL,
                   ap_invoices_pkg.get_approval_status
                                  (ai.invoice_id,
                                   ai.invoice_amount,
                                   ai.payment_status_flag,
                                   ai.invoice_type_lookup_code
                                  ) payment_status,
                   ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
                   --,aid.attribute6 AP_FBT
                   , null ap_fbt
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_invoice_distributions_all aid,
                   ap_invoices_all ai,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND aid.invoice_id = ai.invoice_id
               AND xlajed.source_distribution_id_num_1 =
                                                   aid.invoice_distribution_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category = 'Purchase Invoices'
               AND xlajed.source_distribution_type = 'AP_INV_DIST'
               AND ai.org_id = hou.organization_id
               AND ai.last_updated_by = fu.user_id(+)
               AND ai.vendor_id = pov.vendor_id(+)
               AND ai.vendor_site_id = povs.vendor_site_id(+)
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 10');
  -----------------*********FLAG 11******--------------
         INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '11' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ai.doc_sequence_value, ai.gl_date,
                   ai.invoice_num trx_number, ai.invoice_id,
                   pov.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ai.invoice_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   ai.description narration, pov.vendor_id,
                   pov.segment1 vendor_number, povs.vendor_site_id,
                   povs.vendor_site_code, NULL check_status,
                   NULL check_void_date, cc.account_type,
                   DECODE (ai.attribute_category,
                           'RL Invoice Header', ai.attribute10,
                           NULL
                          ) reference1,
                   jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL,
                   ap_invoices_pkg.get_approval_status
                                  (ai.invoice_id,
                                   ai.invoice_amount,
                                   ai.payment_status_flag,
                                   ai.invoice_type_lookup_code
                                  ) payment_status,
                   ai.invoice_type_lookup_code, jel.je_line_num,jel.attribute3 GL_FBT
                   --,aid.attribute6 AP_FBT
                   , null ap_fbt
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_prepay_app_dists apad,
                   ap_invoice_distributions_all aid,
                   ap_invoices_all ai,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu,
                   DUAL
             WHERE 1 = 1
               AND aid.invoice_id = ai.invoice_id
               AND apad.prepay_app_distribution_id =
                                                   aid.invoice_distribution_id
               AND xlajed.source_distribution_id_num_1 =
                                                       apad.prepay_app_dist_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category = 'Purchase Invoices'
               AND xlajed.source_distribution_type = 'AP_PREPAY'
               AND ai.org_id = hou.organization_id
               AND ai.last_updated_by = fu.user_id(+)
               AND ai.vendor_id = pov.vendor_id(+)
               AND ai.vendor_site_id = povs.vendor_site_id(+)
               AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 11');
  -----------------*********FLAG 12******--------------
         INSERT INTO XX_GL_MV_REPORT
                  (SELECT DISTINCT '12' flag, jel.code_combination_id,
                   NVL (src.user_je_source_name, '**********') SOURCE,
                   gsb.ledger_id, gsb.NAME,
                   NVL (cat.user_je_category_name, '**********') CATEGORY,
                   jel.period_name period_name, jeb.NAME batch_name,
                   jeh.NAME header_name, jel.description description,
                   jeh.currency_code, gsb.currency_code sob_currency_code,
                   xlajel.entered_dr entered_debit,
                   xlajel.entered_cr entered_credit,
                   xlajel.accounted_dr accounted_debit,
                   xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                   INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                   jeh.posted_date, ac.doc_sequence_value,
                   appha.accounting_date gl_date,
                   TO_CHAR (ac.check_number) trx_number, ac.check_id,
                   ac.vendor_name cust_supp_name,
                   xlajel.accounting_date effective_date, ac.check_date,
                   cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                   cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
                   cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
                   cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4,
                   cc.gl_name_seg5, cc.gl_name_seg6, cc.gl_name_seg7,
                   cc.gl_name_seg8, cc.gl_name_seg9, cc.gl_name_seg10,
                   cc.gl_name_seg11, cc.gl_name_seg12, NULL exp_emp_no,
                   NULL project_no, NULL trx_batch_name, jeh.status,
                   rel_acctg_pkg.get_payment_detail (jel.ledger_id,
                                                     ac.check_id,
                                                     'CHECK_ID',
                                                     'DESCR'
                                                    ) narration,
                   ac.vendor_id, pov.segment1 vendor_number,
                   povs.vendor_site_id, povs.vendor_site_code,
                   ac.status_lookup_code check_status,
                   void_date check_void_date, cc.account_type,
                   NULL reference1, jel.creation_date, jeh.je_header_id,
                   jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                   NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
              FROM disc_ccid_dsc_mv cc,
                   gl_ledger_names_v gsb,
                   gl_je_lines jel,
                   gl_je_headers jeh,
                   gl_import_references gir,
                   gl_je_batches jeb,
                   gl_je_categories cat,
                   gl_je_sources src,
                   xla_ae_lines xlajel,
                   xla_ae_headers xlajeh,
                   xla_distribution_links xlajed,
                   ap_payment_hist_dists apphd,
                   ap_payment_history_all appha,
                   ap_checks_all ac,
                   po_vendors pov,
                   po_vendor_sites_all povs,
                   hr_operating_units hou,
                   fnd_user fu
             WHERE 1 = 1
               AND apphd.payment_history_id = appha.payment_history_id
               AND xlajed.source_distribution_type = 'AP_PMT_DIST'
               AND APPHA.TRANSACTION_TYPE=
                                            DECODE((SELECT 1
                                            FROM ap_payment_history_all AX ,
                                            ap_payment_history_all BS
                                            WHERE AX.CHECK_ID=APPHA.CHECK_ID
                                            AND AX.REV_PMT_HIST_ID=BS.PAYMENT_HISTORY_ID
                                            AND AX.TRANSACTION_TYPE='PAYMENT CANCELLED'),'1','PAYMENT CREATED',APPHA.TRANSACTION_TYPE)
               AND xlajed.source_distribution_id_num_1 =apphd.payment_hist_dist_id
               AND xlajel.ae_header_id = xlajed.ae_header_id
               AND xlajel.ae_line_num = xlajed.ae_line_num
               AND xlajel.ae_header_id = xlajeh.ae_header_id
               AND gir.je_header_id = jeh.je_header_id
               AND gir.je_line_num = jel.je_line_num
               AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
               AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
               AND jel.code_combination_id = cc.code_combination_id
               AND jel.status || '' = 'P'
               AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
               AND jeh.je_header_id = jel.je_header_id
               AND jeh.actual_flag = 'A'
               AND jeb.je_batch_id = jeh.je_batch_id
               AND jeh.ledger_id = gsb.ledger_id
               AND jeb.average_journal_flag = 'N'
               AND src.je_source_name = jeh.je_source
               AND cat.je_category_name = jeh.je_category
               AND jeh.je_source = 'Payables'
               AND jeh.je_category IN ('Payments', 'Reconciled Payments')
               AND appha.check_id = ac.check_id
               AND ac.org_id = hou.organization_id
               AND ac.last_updated_by = fu.user_id(+)
               AND ac.vendor_id = pov.vendor_id(+)
               AND ac.vendor_site_id = povs.vendor_site_id(+)
               --AND CC.GL_SEG1=421
               --AND CC.GL_SEG2='515001'
               --AND JEH.JE_HEADER_ID=2213108
                AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

--fnd_file.put_line (fnd_file.output,'DATA LOAD SUCCESFULLY FOR LEGER ID--'||GL_LED_ID.LEDGER_ID||'---'||'--FLAG 12');
 -----------------*********FLAG 13******--------------
         INSERT INTO XX_GL_MV_REPORT
(SELECT '13' flag, jel.code_combination_id,
          NVL (src.user_je_source_name, '**********') SOURCE, gsb.ledger_id,
          gsb.NAME, NVL (cat.user_je_category_name, '**********') CATEGORY,
          jel.period_name period_name, jeb.NAME batch_name,
          jeh.NAME header_name, jel.description description,
          jeh.currency_code, gsb.currency_code sob_currency_code,
          entered_dr entered_debit, entered_cr entered_credit,
          accounted_dr accounted_debit, accounted_cr accounted_credits,
          NULL org_name, INITCAP (fu.description) user_name,
          jeh.doc_sequence_value, jeh.posted_date,
          CASE
             WHEN jeh.je_source = 'Payables'
             AND jeh.je_category = 'Payments'
             AND SUBSTR (jel.description, 1, 1) BETWEEN '0' AND '9'
                THEN TO_NUMBER (jel.description)
             ELSE NULL
          END subledger_voucher_number,
          NULL subledger_gl_date, NULL trx_number, NULL trx_id,
          NULL cust_supp_name, jel.effective_date effective_date,
          jel.effective_date, cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4,
          cc.gl_seg5, cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9,
          cc.gl_seg10, cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1,
          cc.gl_name_seg2, cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
          cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8, cc.gl_name_seg9,
          cc.gl_name_seg10, cc.gl_name_seg11, cc.gl_name_seg12,
          NULL exp_emp_no, NULL project_no, NULL trx_batch_name, jeh.status,
          jel.description narration, NULL vendor_id, NULL vendor_number,
          NULL vendor_site_id, NULL vendor_site_code, NULL check_status,
          NULL check_void_date, cc.account_type, NULL reference1,
          jel.creation_date, jeh.je_header_id, jeh.accrual_rev_je_header_id,
          NULL, NULL, NULL, NULL, jel.je_line_num,jel.attribute3 GL_FBT,null AP_FBT
     FROM disc_ccid_dsc_mv cc,
          gl_ledger_names_v gsb,
          gl_je_lines jel,
          gl_je_headers jeh,
          gl_je_batches jeb,
          gl_je_categories cat,
          gl_je_sources src,
          fnd_user fu
    WHERE 1 = 1
      AND jel.code_combination_id = cc.code_combination_id
      AND jel.status || '' = 'P'
      AND (accounted_dr != 0 OR accounted_cr != 0)
      AND jeh.je_header_id = jel.je_header_id
      AND jeh.actual_flag = 'A'
      AND jeb.je_batch_id = jeh.je_batch_id
      AND jeh.ledger_id = gsb.ledger_id
      AND jeb.average_journal_flag = 'N'
      AND src.je_source_name = jeh.je_source
      AND cat.je_category_name = jeh.je_category
      AND (   jeh.je_source NOT IN ('Receivables', 'Payables')
           OR (       jeh.je_source IN ('Receivables', 'Payables')
                  AND NOT EXISTS (
                           SELECT 'X'
                             FROM gl_import_references gir
                            WHERE 1 = 1
                                  AND gir.je_header_id = jeh.je_header_id)
               OR (    jeh.je_source IN ('Receivables', 'Payables')
                   AND EXISTS (
                          SELECT 'X'
                            FROM gl_import_references gir
                           WHERE 1 = 1
                             AND gir.je_header_id = jeh.je_header_id
                             AND NOT EXISTS (
                                    SELECT 'X'
                                      FROM xla_ae_lines xlajel
                                     WHERE 1 = 1
                                       AND xlajel.gl_sl_link_id =
                                                             gir.gl_sl_link_id))
                  )
              )
          )
      AND jeh.last_updated_by = fu.user_id(+)
      AND GSB.ledger_id=GL_LED_ID.LEDGER_ID------

);

----------------------UNAPP EXCEPTION CASE-----------------
         INSERT INTO XX_GL_MV_REPORT
           (SELECT DISTINCT '19' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits,
                   hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date,
                  acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                  acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              --AND xlajel.Ae_Header_Id=1283555
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajel.ae_line_num = xlajed.ae_line_num
              AND xlajed.source_distribution_id_num_1 IN (50710,50711)
              AND xlajed.source_distribution_id_num_1=ADA.LINE_ID
              AND ada.source_table = 'RA'
              --AND ada.source_type NOT IN ('UNID')
              AND ara.receivable_application_id IN (3862)
              AND ARA.STATUS='UNAPP'
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              --AND jeh.je_category IN ('Receipts', 'Credit Memos')
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              --AND ara.last_updated_by = fu.user_id(+)
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
              AND CC.GL_SEG1='421'-------------
              AND CC.GL_SEG2='416001'
              AND JEH.je_header_id='1692150'
              AND GSB.ledger_id=GL_LED_ID.LEDGER_ID);


----------------------DATA FIX CASE---
         INSERT INTO XX_GL_MV_REPORT
(SELECT
'16' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME HEADER_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR accounted_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
NULL user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
NULL trx_number,
NULL INVOICE_ID,
NULL VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
NULL NVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
GJL.DESCRIPTION narration,
NULL VENDOR_ID,
NULL vendor_number,
NULL VENDOR_SITE_ID,
NULL VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
NULL reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
NULL payment_status,
NULL invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT
XDL_IN.APPLICATION_ID,
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN) AX,
XLA_AE_LINES XAL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_CODE_COMBINATIONS GCC,
disc_ccid_dsc_mv cc
WHERE AX.AE_HEADER_ID IN (2123151,2123149,2123150,2123149,1917207,1917206,1917209,1917205,1917208,1917210,1917211)
AND AX.SOURCE_DISTRIBUTION_TYPE='XLA_MANUAL'
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID);

--------------------------EXCEPTION FOR JOURNAL IS A PART OF MANUAL ENTERY---------------
         INSERT INTO XX_GL_MV_REPORT
              (
SELECT
'17' FLAG,
GCC.CODE_COMBINATION_ID,
GJH.JE_SOURCE SOURCE,
GJH.LEDGER_ID,
(SELECT GSB.NAME FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) NAME,
GJH.JE_CATEGORY CATEGORY,
GJH.PERIOD_NAME,
GJB.NAME batch_name,
GJH.NAME batch_name,
GJL.DESCRIPTION DESCRIPTION,
GJH.CURRENCY_CODE,
(SELECT GSB.CURRENCY_CODE FROM GL_LEDGERS GSB WHERE GSB.LEDGER_ID=GJH.LEDGER_ID) sob_currency_code,
XAL.ENTERED_DR entered_debit,
XAL.ENTERED_CR entered_credit,
XAL.ACCOUNTED_DR accounted_debit,
XAL.ACCOUNTED_CR entered_credit,
(SELECT OU.name FROM HR_OPERATING_UNITS OU WHERE OU.set_of_books_id=GJH.LEDGER_ID) org_name,
INITCAP(fu.description) user_name,
GJH.DOC_SEQUENCE_VALUE,
GJH.posted_date,
NULL doc_sequence_value,
GJL.EFFECTIVE_DATE gl_date,
APA.INVOICE_NUM trx_number,
APA.INVOICE_ID,
ASUP.VENDOR_NAME,
XAL.ACCOUNTING_DATE effective_date,
APA.INVOICE_DATE,
cc.gl_seg1,
cc.gl_seg2,
cc.gl_seg3,
cc.gl_seg4,
cc.gl_seg5,
cc.gl_seg6,
cc.gl_seg7,
cc.gl_seg8,
cc.gl_seg9,
cc.gl_seg10,
cc.gl_seg11,
cc.gl_seg12,
cc.gl_name_seg1,
cc.gl_name_seg2,
cc.gl_name_seg3,
cc.gl_name_seg4,
cc.gl_name_seg5,
cc.gl_name_seg6,
cc.gl_name_seg7,
cc.gl_name_seg8,
cc.gl_name_seg9,
cc.gl_name_seg10,
cc.gl_name_seg11,
cc.gl_name_seg12,
NULL exp_emp_no,
NULL project_no,
NULL trx_batch_name,
GJH.STATUS,
APA.DESCRIPTION narration,
APA.VENDOR_ID,
ASUP.SEGMENT1 vendor_number,
ASA.VENDOR_SITE_ID,
ASA.VENDOR_SITE_CODE,
NULL check_status,
NULL check_void_date,
cc.account_type,
DECODE (APA.attribute_category,
       'RL Invoice Header', APA.attribute10,
       NULL
      ) reference1,
GJL.CREATION_DATE,
GJH.JE_HEADER_ID,
GJH.accrual_rev_je_header_id,
GJH.accrual_rev_period_name,
NULL,
ap_invoices_pkg.get_approval_status
              (APA.invoice_id,
               APA.invoice_amount,
               APA.payment_status_flag,
               APA.invoice_type_lookup_code
              ) payment_status,
APA.invoice_type_lookup_code,
GJL.je_line_num,
GJL.attribute3 GL_FBT,
null ap_fbt
FROM
(SELECT DISTINCT XDL_IN.APPLICATION_ID,----------------
XDL_IN.EVENT_ID,
XDL_IN.AE_HEADER_ID,
XDL_IN.AE_LINE_NUM,
XDL_IN.SOURCE_DISTRIBUTION_TYPE,
XDL_IN.ALLOC_TO_SOURCE_ID_NUM_1
FROM XLA_DISTRIBUTION_LINKS XDL_IN )AX, -----------------
XLA_AE_LINES XAL,
AP_INVOICES_ALL APA ,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
fnd_user fu,
AP_SUPPLIERS ASUP,
disc_ccid_dsc_mv cc,
AP_SUPPLIER_SITES_ALL ASA,
DUAL
WHERE AX.AE_HEADER_ID IN (1478392,1478991,1724120,1726874,1726873,1728823,1728878)
AND AX.AE_HEADER_ID=XAL.AE_HEADER_ID
AND AX.AE_LINE_NUM=XAL.AE_LINE_NUM
AND (XAL.accounted_dr != 0 OR XAL.accounted_cr != 0)
AND AX.ALLOC_TO_SOURCE_ID_NUM_1=APA.INVOICE_ID
AND XAL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.JE_LINE_NUM=AX.AE_LINE_NUM
AND GJH.JE_BATCH_ID=GJB.JE_BATCH_ID
AND APA.LAST_UPDATED_BY=fu.user_id(+)
AND ASUP.VENDOR_ID=APA.VENDOR_ID
AND CC.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
AND ASA.VENDOR_SITE_ID=APA.VENDOR_SITE_ID
AND GJH.LEDGER_ID=GL_LED_ID.LEDGER_ID
);
--------------------FOR ACCOUNT 754001
INSERT INTO XX_GL_MV_REPORT
(SELECT DISTINCT '15' flag, jel.code_combination_id,
                  NVL (src.user_je_source_name, '**********') SOURCE,
                  gsb.ledger_id, gsb.NAME,
                  NVL (cat.user_je_category_name, '**********') CATEGORY,
                  jel.period_name period_name, jeb.NAME batch_name,
                  jeh.NAME header_name, jel.description description,
                  jeh.currency_code, gsb.currency_code sob_currency_code,
                  xlajel.entered_dr entered_debit,
                  xlajel.entered_cr entered_credit,
                  xlajel.accounted_dr accounted_debit,
                  xlajel.accounted_cr accounted_credits, hou.NAME org_name,
                  INITCAP (fu.description) user_name, jeh.doc_sequence_value,
                  jeh.posted_date, acr.doc_sequence_value,
                  jeh.posted_date gl_date,
                                          --ara.gl_date,
                                          acr.receipt_number trx_number,
                  acr.cash_receipt_id, rc.customer_name cust_supp_name,
                  xlajel.accounting_date effective_date, acr.receipt_date,
                  cc.gl_seg1, cc.gl_seg2, cc.gl_seg3, cc.gl_seg4, cc.gl_seg5,
                  cc.gl_seg6, cc.gl_seg7, cc.gl_seg8, cc.gl_seg9, cc.gl_seg10,
                  cc.gl_seg11, cc.gl_seg12, cc.gl_name_seg1, cc.gl_name_seg2,
                  cc.gl_name_seg3, cc.gl_name_seg4, cc.gl_name_seg5,
                  cc.gl_name_seg6, cc.gl_name_seg7, cc.gl_name_seg8,
                  cc.gl_name_seg9, cc.gl_name_seg10, cc.gl_name_seg11,
                  cc.gl_name_seg12, NULL exp_emp_no, NULL project_no,
                  NULL trx_batch_name, jeh.status,
                  NVL (acr.comments, acr.reversal_comments) narration,
                  rc.customer_id, rc.customer_number, hcsu.site_use_id,
                  hcsu.site_use_code, acr.status check_status,
                  acr.reversal_date check_void_date, cc.account_type,
                  NULL reference1, jel.creation_date, jeh.je_header_id,
                  jeh.accrual_rev_je_header_id, jeh.accrual_rev_period_name,
                  NULL, NULL, NULL, jel.je_line_num, jel.attribute3 gl_fbt,
                  NULL ap_fbt
             FROM disc_ccid_dsc_mv cc,
                  gl_ledger_names_v gsb,
                  gl_je_lines jel,
                  gl_je_headers jeh,
                  gl_import_references gir,
                  gl_je_batches jeb,
                  gl_je_categories cat,
                  gl_je_sources src,
                  xla_ae_lines xlajel,
                  xla_distribution_links xlajed,
                  ar_distributions_all ada,
                  ar_receivable_applications_all ara,
                  ar_cash_receipts_all acr,
                  hr_operating_units hou,
                  fnd_user fu,
                  hz_cust_site_uses_all hcsu,
                  ar_customers rc,
                  DUAL
            WHERE 1 = 1
              AND xlajel.ae_header_id = xlajed.ae_header_id
              AND xlajed.source_distribution_id_num_1 = ada.line_id
              AND ada.source_table = 'RA'
              AND ada.source_id = ara.receivable_application_id
              AND ara.cash_receipt_id = acr.cash_receipt_id
              AND gir.je_header_id = jeh.je_header_id
              AND gir.je_line_num = jel.je_line_num
              AND gir.gl_sl_link_id = xlajel.gl_sl_link_id
              AND gir.gl_sl_link_table = xlajel.gl_sl_link_table
              AND jel.code_combination_id = cc.code_combination_id
              AND jel.status || '' = 'P'
              AND (xlajel.accounted_dr != 0 OR xlajel.accounted_cr != 0)
              AND jeh.je_header_id = jel.je_header_id
              AND jeh.actual_flag = 'A'
              AND jeb.je_batch_id = jeh.je_batch_id
              AND jeh.ledger_id = gsb.ledger_id
              AND jeb.average_journal_flag = 'N'
              AND src.je_source_name = jeh.je_source
              AND cat.je_category_name = jeh.je_category
              AND jeh.je_source = 'Receivables'
              AND jeh.je_category IN
                                  ('Receipts', 'Credit Memos', 'Debit Memos')
              AND xlajed.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
              AND ara.org_id = hou.organization_id
              AND jeh.last_updated_by = fu.user_id(+)
              AND acr.pay_from_customer = rc.customer_id(+)
              AND acr.customer_site_use_id = hcsu.site_use_id(+)
              AND CC.GL_SEG2='754001'
              AND CC.GL_SEG1=(SELECT DECODE(GL_LED_ID.LEDGER_ID,'2027','421','431') FROM DUAL)
              );



COMMIT;
fnd_file.put_line (fnd_file.LOG,'LOAD SUCCSSFULLY FOR-'||GL_LED_ID.LEDGER_ID);
END LOOP;
fnd_file.put_line (fnd_file.LOG,'DATA LOAD SUCCESFULLY');
EXCEPTION WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'ERROR WHILE LOADING DATA');
END;
/

No comments:

Post a Comment