GL Subledger Accounting in R12

create or replace function BG_PRC_JOURNAL_DESCRIPTION(p_header_id number,
                                                      p_line_num  number,
                                                      p_source    varchar2,
                                                      p_category  varchar2)
  return varchar2 is

  v_desc         varchar2(1000);
  v_dummy_output varchar2(1000);
  v_batch_name   gl_je_batches.name%TYPE;
  v_Reference_1  varchar2(1000);
  v_Reference_2  varchar2(1000);
  v_Reference_3  varchar2(1000);
  v_Reference_4  varchar2(1000);
  v_Reference_5  varchar2(1000);
  --Added by Dennis for R12 Upgrade--
  v_gl_sl_link_table varchar2(100);
  v_gl_sl_link_id    varchar2(100);
  v_ledger_ID        number;
  --Added by Dennis for R12 Upgrade--

  rec_journal_header gl_je_headers%rowtype;
  v_line_description gl_je_lines.description%TYPE;

begin

  select *
    into rec_journal_header
    from gl_je_headers
   where je_header_id = p_header_id;

  select description, ledger_ID
    into v_line_description, v_ledger_ID
    from gl_je_lines
   where je_header_id = p_header_id
     and je_line_num = p_line_num;

  if (p_source = 'Purchasing' and p_category = 'Receiving') or
     (p_source = 'Payables' and
     p_category in ('Payments', 'Purchase Invoices')) or
     (p_source = 'Receivables' and
     p_category in ('Sales Invoices',
                     'Credit Memos',
                     'Debit Memos',
                     'Credit Memo Applications',
                     'Misc Receipts',
                     'Trade Receipts',
                     'Cross Currency')) or
     (p_source = 'Assets' and p_category <> 'Depreciation') then
 
    select gir.reference_1,
           gir.reference_2,
           gir.reference_3,
           gir.reference_4,
           gir.reference_5,
           --Start Added by Dennis for R12 Upgrade--
           gl_sl_link_table,
           gl_sl_link_id
    --End Added by Dennis for R12 Upgrade--
      into v_Reference_1,
           v_Reference_2,
           v_Reference_3,
           v_Reference_4,
           v_Reference_5,
    --Start Added by Dennis for R12 Upgrade--
           v_gl_sl_link_table,
           v_gl_sl_link_id
    --End Added by Dennis for R12 Upgrade--
      from gl_import_references gir
     where gir.je_header_id = p_header_id
       and gir.je_line_num = p_line_num
          --Start Added by Dennis for R12 Upgrade-- 
       and gl_sl_link_table is not null
       and gl_sl_link_id is not null;
    --End Added by Dennis for R12 Upgrade--
 
  end if;

  --PO--
 --&gt>>> In R12 version the source name replace with "Cost Management"---<<<
  --if p_source ='Purchasing' and p_category = 'Receiving' then
 
  if p_source ='Cost Management' and p_category = 'Receiving' then
    /*select rsh.receipt_num || '/' || rsh.shipment_num || '/' || vendor_name
     into v_desc
     from po_headers_all       poh,
          po_vendors           pov,
          rcv_shipment_headers rsh,
          rcv_shipment_lines   rsl,
          rcv_transactions     rcv
    where poh.vendor_id = pov.vendor_id
      and poh.po_header_id = v_Reference_2
      and rsh.shipment_header_id = rsl.shipment_header_id
      and rsl.po_header_id = poh.po_header_id
      and rsl.po_distribution_id = v_Reference_3
      and rcv.transaction_id =v_Reference_5
      and rcv.shipment_header_id = rsl.shipment_header_id
      and rcv.shipment_line_id = rsl.shipment_line_id;*/ --comment by Dennis
      --gl_sl_link_table：RSL-
    --Start Added by Dennis for R12 Upgrade-- 
    SELECT rsh.receipt_num || '/' || rsh.shipment_num || '/' || pov.vendor_name
      into v_desc
   
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           po_headers_all           poh,
           po_vendors               pov,
           rcv_shipment_headers     rsh,
           rcv_shipment_lines       rsl,
           rcv_transactions         rcv
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'RCV_ACCOUNTING_EVENTS'
       AND ATE.source_id_int_1(+) = rcv.transaction_id
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and poh.vendor_id = pov.vendor_id
          --and poh.po_header_id = v_Reference_2
       and rsh.shipment_header_id = rsl.shipment_header_id
       and rsl.po_header_id = poh.po_header_id
          --and rsl.po_distribution_id = v_Reference_3
          --and rcv.transaction_id =v_Reference_5
       and rcv.shipment_header_id = rsl.shipment_header_id
       and rcv.shipment_line_id = rsl.shipment_line_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --AP Invoices
  elsif p_source = 'Payables' and p_category = 'Purchase Invoices' then
    /* select invoice_num || '/' || to_char(invoice_date, 'dd-mm-yyyy') || '/' ||
          pov.vendor_name || '/' || description
     into v_desc
     from ap_invoices_all api, po_vendors pov
    where api.vendor_id = pov.vendor_id
     and invoice_id = v_Reference_2*/ --comment by Dennis
    --gl_sl_link_table：APECL-
    --Start Added by Dennis for R12 Upgrade-- 
    SELECT AI.invoice_num || '/' || to_char(AI.invoice_date, 'dd-mm-yyyy') || '/' ||
           pov.vendor_name || '/' || AI.description
      into v_desc
   
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           AP_INVOICES_ALL          AI,
           po_vendors               pov
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'AP_INVOICES'
       AND ATE.source_id_int_1(+) = AI.Invoice_ID
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and ai.vendor_id = pov.vendor_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --AP Payments
  elsif p_source = 'Payables' and p_category = 'Payments' then
    /*select check_number || '/' || pov.vendor_name || '/' ||
          apc.bank_account_num
     into v_desc
     from ap_checks_all apc, po_vendors pov
    where apc.vendor_id = pov.vendor_id
      and check_id = v_Reference_3;*/ --comment by Dennis
    --gl_sl_link_table：APECL-
    --Start Added by Dennis for R12 Upgrade--
    SELECT apc.check_number || '/' || pov.vendor_name || '/' ||
           apc.bank_account_num
      into v_desc
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           ap_checks_all            apc,
           po_vendors               pov
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'AP_PAYMENTS'
       AND ATE.source_id_int_1(+) = apc.check_id --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and apc.vendor_id = pov.vendor_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --AR Invoices/CM/DM--
    --gl_sl_link_table：XLAJEL--
  elsif p_source = 'Receivables' and
        p_category in ('Sales Invoices', 'Credit Memos', 'Debit Memos') then
    /* select trx_number || '/' || rc.customer_name || '/' ||
          to_char(trx_date, 'dd-mm-yyyy')
     into v_desc
     from ra_customer_trx_all rctl, ra_customers rc
    where rctl.bill_to_customer_id = rc.customer_id
      and rctl.customer_trx_id = v_Reference_2;*/ --comment by Dennis
 
    --Start Added by Dennis for R12 Upgrade--
    SELECT rctl.trx_number || '/' || rc.customer_name || '/' ||
           to_char(rctl.trx_date, 'dd-mm-yyyy')
      into v_desc
   
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           ra_customer_trx_all      rctl,
           ar_customers             rc --upgrade R12--
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'TRANSACTIONS'
       AND ATE.source_id_int_1(+) = rctl.CUSTOMER_TRX_ID --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and rctl.bill_to_customer_id = rc.customer_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --AR CM Applications
  elsif p_source = 'Receivables' and
        p_category = 'Credit Memo Applications' then
    /*    select 'CM:' || trx_number || '/' || rc.customer_name || '/' ||
          to_char(trx_date, 'dd-mm-yyyy')
     into v_desc
     from ra_customer_trx_all rctl, ra_customers rc
    where rctl.bill_to_customer_id = rc.customer_id
      and rctl.trx_number = v_Reference_4;*/ --comment by Dennis
 
    --Start Added by Dennis for R12 Upgrade--
    SELECT 'CM:' || rctl.trx_number || '/' || rc.customer_name || '/' ||
           to_char(rctl.trx_date, 'dd-mm-yyyy')
      into v_desc
   
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           ra_customer_trx_all      rctl,
           ar_customers             rc --upgrade R12--
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'TRANSACTIONS'
       AND ATE.source_id_int_1(+) = rctl.CUSTOMER_TRX_ID --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and rctl.bill_to_customer_id = rc.customer_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--  
 
    /*  select 'Invoice:' || trx_number || '/' || rc.customer_name || '/' ||
          to_char(trx_date, 'dd-mm-yyyy')
     into v_dummy_output
     from ra_customer_trx_all rctl, ra_customers rc
    where rctl.bill_to_customer_id = rc.customer_id
      and rctl.trx_number = v_Reference_5;*/ --comment by Dennis
 
    --Start Added by Dennis for R12 Upgrade--  
    SELECT rctl.trx_number || '/' || rc.customer_name || '/' ||
           to_char(rctl.trx_date, 'dd-mm-yyyy')
      into v_dummy_output
   
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           ra_customer_trx_all      rctl,
           ar_customers             rc --upgrade R12--
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'TRANSACTIONS'
       AND ATE.source_id_int_1(+) = rctl.CUSTOMER_TRX_ID --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       and rctl.bill_to_customer_id = rc.customer_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
    v_desc := v_desc || ' ' || v_dummy_output;
 
    --AR Misc Receipts
  elsif p_source = 'Receivables' and p_category = 'Misc Receipts' then
    /*  select receipt_number || '/' || art.name || '/' || bank_account_num
     into v_desc
     from ar_cash_receipts_all   acr,
          ap_bank_accounts_all aba ,
          ar_receivables_trx_all art
    where acr.remittance_bank_account_id = aba.bank_account_id(+)
      and acr.receivables_trx_id = art.receivables_trx_id
      and acr.cash_receipt_id = v_Reference_2;*/ --comment by Dennis
     --gl_sl_link_table：'XLAJEL'  
    --Start Added by Dennis for R12 Upgrade--
    SELECT acr.receipt_number || '/' || art.name || '/' ||
           aba.bank_account_num
      into v_desc
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           ar_cash_receipts_all     acr,
           ce_bank_accounts         aba, --upgrade R12--
           ar_receivables_trx_all   art
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'RECEIPTS'
       AND ATE.source_id_int_1(+) = acr.cash_receipt_id --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
          -- and acr.remittance_bank_account_id = aba.bank_account_id(+)
       and acr.remit_bank_acct_use_id = aba.bank_account_id(+)
       and acr.receivables_trx_id = art.receivables_trx_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --AR Trade Receipts/Cross Currency
  elsif p_source = 'Receivables' and
        p_category in ('Trade Receipts', 'Cross Currency') then
    /*select receipt_number || '/' || party_name || '/' || bank_account_num
        into v_desc
        from ar_cash_receipts_all acr,
             ap_bank_accounts_all aba,
             hz_cust_accounts     cust,
             hz_parties           party
       where acr.remittance_bank_account_id = aba.bank_account_id(+)
         and cust.party_id = party.party_id
         and acr.pay_from_customer = cust.cust_account_id
         and acr.cash_receipt_id =
             to_number(substr(v_Reference_2, 1, instr(v_Reference_2, 'C') - 1));
    */ --comment by Dennis
     --gl_sl_link_table：'XLAJEL'
    --Start Added by Dennis for R12 Upgrade--
    SELECT acr.receipt_number || '/' || party.party_name || '/' ||
           aba.bank_account_num
      into v_desc
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           --
           ar_cash_receipts_all acr,
           ce_bank_accounts     aba, --upgrade R12--
           hz_cust_accounts     cust,
           hz_parties           party
    --
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'RECEIPTS'
       AND ATE.source_id_int_1(+) = acr.cash_receipt_id --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
          -- and acr.remittance_bank_account_id = aba.bank_account_id(+)
       and acr.remit_bank_acct_use_id = aba.bank_account_id(+)
       and cust.party_id = party.party_id
       and acr.pay_from_customer = cust.cust_account_id
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --FA Assets--
  elsif p_source = 'Assets' and p_category <> 'Depreciation' then
    /* select asset_number || '/' || rec_journal_header.Description
     into v_desc
     from fa_additions
    where asset_id = 72849v_Reference_2;*/ --comment by Dennis
     --gl_sl_link_table：'XLAJEL'
    --Start Added by Dennis for R12 Upgrade--
    SELECT f.asset_number || '/' || rec_journal_header.Description
      into v_desc
      FROM xla_transaction_entities ATE,
           xla_events               AAE,
           xla_ae_headers           AEH,
           xla_ae_lines             AEL,
           --
           fa_transactions_v f
    --
     WHERE ATE.entity_id = AAE.entity_id
       AND ATE.entity_code = 'TRANSACTIONS'
       AND ATE.source_id_int_1(+) = f.transaction_header_id --
       AND AAE.event_id = AEH.event_id
       AND AEH.ae_header_id = AEL.ae_header_id
          --
       AND AEH.Ledger_ID = v_ledger_ID
          --
       and ael.gl_sl_link_table = v_gl_sl_link_table
       and AEL.gl_sl_link_id = v_gl_sl_link_id;
    --End Added by Dennis for R12 Upgrade--
 
    --Inventory--
  elsif p_source = 'Inventory' then
    -- and p_category = 'MTL' then
 --&gt>>> In R12 version the source name replace with "Cost Management"---<<<
  --Source :Cost Management ,Category :MTL
  -- MTA
    select name
      into v_batch_name
      from gl_je_batches
     where je_batch_id = rec_journal_header.je_batch_id;
 
    v_desc := v_batch_name || '/' || rec_journal_header.Name; --batch name + gl journal name
 
    /*select  mmt.transaction_type_id          Transaction_type_id,
              mmt.reason_id                    Reason_id,
            mtr.reason_name                  Reason_name ,
            mmt.shipment_number              GRN_number,
            mmt.source_line_id               so_line_id
    into v_tran_type, v_reason_id, v_reason_name, v_grn, v_so_line
    from  mtl_material_transactions mmt, mtl_transaction_reasons mtr
    where mmt.transaction_id = :Reference_3
    and   mmt.reason_id = mtr.reason_id(+);
   
       --PO Receipt/PO Rcpt Adjust
             if v_tran_type in (18,71) then
                 v_desc := v_grn;
             --Return to Vendor
             elsif v_tran_type = 36 then
               v_desc := 'RTV';
             --Backflush Transfer/Miscellaneous issue
             elsif v_tran_type in (32,51) then
                 v_desc := v_reason_name;
             --21 INV - FG Receipt / 27 INV - Receipt from FA / 81 WIP - Normal move
             elsif v_reason_id in (1,629,10) then
                 v_desc := v_reason_name;
             --Sales Order Pick/Sales order issue/RMA Receipt/RMA Return
             elsif v_tran_type in (15,37,33,52) then
                   select trx_number into v_desc
                   from ra_customer_trx_all  where interface_header_attribute6 = v_so_line;
             else
               v_desc := :Description; --gl description
             end if;*/
 
    --Manual
  elsif p_source = 'Manual' or p_source = 'Spreadsheet' then
    v_desc := v_line_description; --je line description
 
  else
    v_desc := rec_journal_header.Description; --gl header description
 
  end if;

  return v_desc;

Exception
  When others then
    v_desc := rec_journal_header.Description; --gl header description
    return v_desc;
end;

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-705051/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-705051/