اخواني الاعزاء تحية طيبة وبعد ,,,

من لديه معرفة جيدة بربط ال

ap
ar
fix assets
inv
po

مع ال gl

حيث مطلوب مني عمل تقرير يظهر جميع الحركات التصيليه وجميع ال transaction التي تم ترحيلها لل gl

اخواني الاعزاء تحية طيبة وبعد ,,,

من لديه معرفة جيدة بربط ال

ap
ar
fix assets
inv
po

مع ال gl

حيث مطلوب مني عمل تقرير يظهر جميع الحركات التصيليه وجميع ال transaction التي تم ترحيلها لل gl

طيب ممكن توضح بالتفاصيل انت محتاج تعمل تقرير عن اية

يعنى مثلا لو قولنا AP , AR
انت عاوز تعرف اية Invoice , Transaction , Payment , Recipt القيود اللى راحت من المديولات دى الى GL ؟؟


لو لسه موصلتش لحد ممكن تكتب ونتكلم و ان شاء الله نقدر نحل ليك يا هندسة المهام دى ... والله المستعان

ممكن تستعمل السلكتات ال جو الفانكشن دية وهتلاقية فيها طريقة ربط ال gl بال sla

ثم ربطة بالحركة نفسها حسب نوعها

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--
-->>>> 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
-->>>> 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;

تم تعديل 28 أغسطس 201213 سنة بواسطة hossam160

.....................زز

تم تعديل 28 أغسطس 201213 سنة بواسطة hossam160

مشكور