Query for PR PO Receipt Invoice Payments in Orecle EBS R12

--PR and PO (Standard)

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_header_id = pla.po_header_id and po_line_id = pla.po_line_id)

order by 3 desc;

--PR, PO (Standard) and Receipts

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and  exists

                (select 1

                from ap_invoice_lines_all

                where 1=1

                and po_line_id = pla.po_line_id

                and po_header_id = pla.po_header_id)

order by 3 desc

---PR, PO (Standard), Receipts and Invoice

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

          rt.transaction_id,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt,

        ap_invoice_lines_all aila,

        ap_invoices_all aia

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and  prha.requisition_header_id= 48955358

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

order by 3 desc;

---PR, PO (Standard), Receipts, Invoice and Payments

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,

         pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

         pha.cancel_flag po_cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

          rt.transaction_id,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda,

        po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and prda.distribution_id  = pda.req_distribution_id

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

and pha.type_lookup_code = 'STANDARD'

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

--and  prha.requisition_header_id= 48955358

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

order by 3 desc;

--PR, PO (Blanket) and Releases

select pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

        (select sum(nvl(quantity,0)*nvl(price_override,0))

        from po_line_locations_all plla

        where 1=1

        and plla.po_release_id = pra.po_release_id) release_value,        

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

--and pra.po_release_id = 17622990

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_release_id = pra.po_release_id)

and not exists  (select 1 from ap_invoice_lines_all  where 1=1 and po_release_id = pra.po_release_id);

begin mo_global.set_policy_context('S',125); end;

--PR, PO (Blanket), Releases and Receipts

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and not exists  (select 1 from ap_invoice_lines_all  where 1=1 and po_release_id = pra.po_release_id)

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

--- PR, PO (Blanket), Releases, Receipts and Invoices

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          aila.line_number invoice_line_no,

          aila.amount,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date        

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        ap_invoice_lines_all aila,

        ap_invoices_all aia           

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and aila.po_release_id = pra.po_release_id

and aila.po_line_location_id = plla.line_location_id

and aila.invoice_id = aia.invoice_id

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

--- PR, PO (Blanket), Releases, Receipts, Invoices and Payments

select distinct pda.requisition_header_id,

         pda.segment1 pr_num,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag pr_cancel_flag,        

         (prla.unit_price*prla.quantity)   pr_value,

         pra.po_header_id,

         pra.po_num,

         pra.revision_num po_revisison_no,

         pra.type_lookup_code po_type,

         pda.encumbered_amount po_line_amount,

         pra.po_release_id,

         pra.release_num,

         pra.release_revision_num,

         pra.authorization_status release_status,

         pra.total release_value,

         trunc(pra.release_date) release_date,

         pra.cancel_date,

         pra.cancel_reason,

         pra.cancel_flag,

        plla.line_location_id,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         plla.unit_price*plla.quantity_received receipt_amount,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          aila.line_number invoice_line_no,

          aila.amount,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,

          trunc(aia.cancelled_date) invoice_cancellation_date ,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

         aca.amount payment_value                    

from po_releases_v pra,

        po_distributions_v pda,

        po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_line_locations_release_v plla,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca                    

where 1=1

and trunc(pra.release_date) >= trunc(sysdate)-365

and pra.po_release_id = pda.po_release_id

and pda.requisition_header_id = prha.requisition_header_id

and prha.requisition_header_id = prla.requisition_header_id

and pda.requisition_line_id = prla.requisition_line_id

and pra.po_release_id = plla.po_release_id

and plla.line_location_id = pda.line_location_id

and pra.po_release_id = rsl.po_release_id

and plla.line_location_id = rsl.po_line_location_id

and rsl.shipment_header_id = rsh.shipment_header_id

and aila.po_release_id = pra.po_release_id

and aila.po_line_location_id = plla.line_location_id

and aila.invoice_id = aia.invoice_id

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

--and trunc(pra.release_date) between '01-JAN-2016' and '14-JUN-2016'

--and pra.po_release_id = 17622990

begin mo_global.set_policy_context('S',125); end;

/* Only PR */

select distinct prha.requisition_header_id,

         prha.segment1 pr_number,

         trunc(prha.creation_date) pr_creation_date,

         (select sum(unit_price*quantity)

         from po_requisition_lines_all

         where 1=1

         and requisition_header_id = prha.requisition_header_id) pr_value,

         decode(prha.attribute_category, 'Yes', 'Placement', 'iProcurment') source,

         initcap(prha.authorization_status) pr_status,

         prha.cancel_flag

from po_requisition_headers_all prha,

        po_requisition_lines_all prla,

        po_req_distributions_all prda

where 1=1

and prha.requisition_header_id = prla.requisition_header_id

and prla.requisition_line_id = prda.requisition_line_id

and trunc(prha.creation_date) >= trunc(sysdate)-365

and not exists  (select 1 from po_distributions_all pda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* Only PO */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

and not exists (select 1 from rcv_shipment_lines where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

and not exists (select 1 from rcv_transactions where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

and not exists (select 1 from ap_invoice_lines_all where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

order by 3 desc

/* PO and Receipts */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id       

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt       

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

and not exists (select 1 from ap_invoice_lines_all where 1=1 and po_header_id = pha.po_header_id and pla.po_line_id = po_line_id)

order by 3 desc

/* PO, Receipts and Invoices */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id ,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,                   

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount              

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt ,

        ap_invoice_lines_all aila,

        ap_invoices_all aia             

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* PO, Receipt, Invoice and Payments */

select distinct pha.po_header_id,

         pha.segment1 po_number,

         pha.type_lookup_code po_type,

         trunc(pha.creation_date) po_creation_date,

         (select sum(unit_price*quantity)

         from po_lines_all

         where 1=1

         and po_header_id = pha.po_header_id) po_value,

         initcap(pha.authorization_status) po_status,

        pha.cancel_flag,

         rsh.shipment_header_id ,

         rsh.receipt_num,

         trunc(rsh.creation_date) receipt_creation_date,

         (select sum(rsl.quantity_received*pla.unit_price)

         from rcv_shipment_lines

         where 1=1

         and shipment_header_id = rsl.shipment_header_id) receipt_amount,

         rt.transaction_id ,

          aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          aia.amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,                   

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount ,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value                       

from po_headers_all pha,

        po_lines_all pla,

        po_distributions_all pda,

        rcv_shipment_lines rsl,

        rcv_shipment_headers rsh,

        rcv_transactions rt ,

        ap_invoice_lines_all aila,

        ap_invoices_all aia,

        ap_invoice_payments_all aipa,

        ap_checks_all aca                     

where 1=1

and pha.po_header_id = pla.po_header_id

and pla.po_line_id = pda.po_line_id

--and pha.type_lookup_code = 'STANDARD'      

and trunc(pha.creation_date) >= trunc(sysdate)-365

and pha.po_header_id = rsl.po_header_id

and rsl.po_line_id = pla.po_line_id

and rsl.shipment_header_id = rsh.shipment_header_id

and rt.shipment_line_id = rsl.shipment_line_id

and rt.shipment_header_id = rsh.shipment_header_id

and aila.po_line_id = pla.po_line_id

and aila.po_header_id = pla.po_header_id

and aila.invoice_id = aia.invoice_id

and rt.transaction_id = aila.rcv_transaction_id(+)

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

and not exists  (select 1 from po_req_distributions_all prda where 1=1 and pda.req_distribution_id = prda.distribution_id)

order by 3 desc

/* Invoices Only */

select distinct aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          nvl(aia.amount_paid,0) amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount

from ap_invoices_all aia,

        ap_invoice_lines_all aila

where 1=1

and aia.invoice_id = aila.invoice_id

and trunc(aia.creation_date) >= trunc(sysdate)-365

and aila.po_line_id is null

and not exists (select 1 from ap_invoice_payments_all where 1=1 and invoice_id = aia.invoice_id)

order by 5 desc

/* Invoice and Payments */

select distinct aia.invoice_id,

          aia.invoice_num,

          aia.invoice_amount,

          nvl(aia.amount_paid,0) amount_paid,

          trunc(aia.creation_date) invoice_creation_date,

          trunc(aia.invoice_date) invoice_date,

          aia.gl_date,         

          trunc(aia.cancelled_date) invoice_cancellation_date,

          aia.cancelled_amount,

          aca.check_id,

          aca.check_number payment_number,

          trunc(aca.creation_date) payment_creation_date,

          aca.amount payment_value         

from ap_invoices_all aia,

        ap_invoice_lines_all aila,

        ap_invoice_payments_all aipa,

        ap_checks_all aca           

where 1=1

and aia.invoice_id = aila.invoice_id

and trunc(aia.creation_date) >= trunc(sysdate)-365

and aila.po_line_id is null

and aia.invoice_id = aipa.invoice_id

and aipa.check_id = aca.check_id

order by 5 desc

Adding Resp Via API / Deleting Executable and concurrent Program

 

 DECLARE

   lv_user_name        VARCHAR2 (20) := 'SENTHIL';

   lv_req_resp_key     VARCHAR2 (50); -- := 'APPLICATION_DEVELOPER';

   lv_description      VARCHAR2 (100) := 'Via script added';

   lv_req_resp_name    VARCHAR2 (200);

   lv_appl_shrt_name   VARCHAR2 (20);

   lv_appl_name        VARCHAR2 (50);

   lv_resp_key         VARCHAR2 (50);

  

cursor c1 is

    

    SELECT fav.application_short_name,

          fav.application_name,

          upper(frv.responsibility_name) responsibility_name,

          frv.responsibility_key

     --INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

     FROM fnd_application_vl fav, fnd_responsibility_vl frv

    WHERE frv.application_id = fav.application_id

    and upper(frv.responsibility_name) in

    (

     'FUNCTIONAL ADMINISTRATOR'

     ,'AP PAYABLES SUPER USER'   

    );

BEGIN

    DBMS_OUTPUT.put_line ('Below Responsibility Added for this user '|| lv_user_name);

    DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

    for rec in c1 

   loop

   /*SELECT fav.application_short_name,

          fav.application_name,

          frv.responsibility_name

     INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

     FROM fnd_application_vl fav, fnd_responsibility_vl frv

    WHERE frv.application_id = fav.application_id

      AND frv.responsibility_key = lv_req_resp_key; */

   fnd_user_pkg.addresp (username         => lv_user_name,

                         resp_app         => rec.application_short_name, --lv_appl_shrt_name,

                         resp_key         => rec.responsibility_key , --lv_req_resp_key,

                         security_group   => 'STANDARD',

                         description      => lv_description,

                         start_date       => SYSDATE,

                         end_date         => NULL

                                              );

   COMMIT;

  

   DBMS_OUTPUT.put_line (rec.responsibility_name);

 end loop;

  DBMS_OUTPUT.put_line ('--------------------------------------------------------------');

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

      ROLLBACK;

END;

/* Deleting Executable and concurrent Program*/

SET SERVEROUTPUT ON;
 
DECLARE
   l_prog_short_name   VARCHAR2 (240);
   l_exec_short_name   VARCHAR2 (240);
   l_appl_full_name    VARCHAR2 (240);
   l_appl_short_name   VARCHAR2 (240);
   l_del_prog_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
   l_del_exec_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
   --
   -- set concurrent program and executable short name
   --
   l_prog_short_name := 'XX_TEST_CP';             -- Concurrent program short name
   l_exec_short_name := 'XX_TEST_EXEC';           -- Executable short name
   l_appl_full_name := 'XX Custom Application';   -- Application full name
   l_appl_short_name := 'XXCUST';                 -- Application Short name
 
   --
   -- Check if the program exists. if found, delete the program
   --
   IF     fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
      AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
   THEN
 
      IF l_del_prog_flag = 'Y'
	  THEN
      --
      --API call to delete Concurrent Program
      --
      fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
	  --
	  END IF;
	  --
      IF l_del_exec_flag = 'Y'
	  THEN
      --
      --API call to delete Executable
      --
      fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
	  --
	  END IF;
      COMMIT;
	  --
      DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
      DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
   --
   -- if the program does not exist in the system
   --
   ELSE
      DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;

/* Delete XML Publisher Definition and Template*/

SET SERVEROUTPUT ON

 

DECLARE

   -- Change the following two parameters

   var_templateCode    VARCHAR2 (100) := 'XX_US_BEN_NOT_EXP_ELEC';     -- Template Code

   boo_deleteDataDef   BOOLEAN := TRUE;     -- delete the associated Data Def.

BEGIN

   FOR RS

      IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,

                 T1.DATA_SOURCE_CODE,

                 T2.APPLICATION_SHORT_NAME DEF_APP_NAME

            FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2

           WHERE T1.TEMPLATE_CODE = var_templateCode

                 AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)

   LOOP

      XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);

 

      DELETE FROM XDO_LOBS

            WHERE     LOB_CODE = var_templateCode

                  AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

 

      DELETE FROM XDO_CONFIG_VALUES

            WHERE     APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME

                  AND TEMPLATE_CODE = var_templateCode

                  AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                  AND CONFIG_LEVEL = 50;

 

      DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');

 

      IF boo_deleteDataDef

      THEN

         XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,

                                            RS.DATA_SOURCE_CODE);

 

         DELETE FROM XDO_LOBS

               WHERE LOB_CODE = RS.DATA_SOURCE_CODE

                     AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND LOB_TYPE IN

                            ('XML_SCHEMA',

                             'DATA_TEMPLATE',

                             'XML_SAMPLE',

                             'BURSTING_FILE');

 

         DELETE FROM XDO_CONFIG_VALUES

               WHERE     APPLICATION_SHORT_NAME = RS.DEF_APP_NAME

                     AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE

                     AND CONFIG_LEVEL = 30;

 

         DBMS_OUTPUT.PUT_LINE (

            'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');

      END IF;

   END LOOP;

 

   DBMS_OUTPUT.PUT_LINE (

      'Issue a COMMIT to make the changes or ROLLBACK to revert.');

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

      DBMS_OUTPUT.PUT_LINE (

         'Unable to delete XML Publisher Template ' || var_templateCode);

      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));

END;

Query to find Responsibility for the Concurrent Program

 SELECT distinct frt.responsibility_name,

               frg.request_group_name,

               frg.description,fcpt.user_concurrent_program_name

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

 --AND   fcp.concurrent_program_name = <shortname>

 AND   fcpt.user_concurrent_program_name LIKE '%XXX%Pre%%%'

 and frt.responsibility_name like '%XX_PO%'

Query for item onhand quantity & Lot wise query

 

=============Query for item onhand quantity===================

SELECT  msi.segment1 item_name,mq.subinventory_code, b.SEGMENT1||'.'||b.SEGMENT2||'.'||b.SEGMENT3||'.'||b.SEGMENT4 locator,
SUM (mq.transaction_quantity) on_hand,msi.INVENTORY_ITEM_ID,msi.organization_id
    FROM apps.org_organization_definitions ood,
         apps.mtl_onhand_quantities mq,
         apps.mtl_system_items_b msi,
         mtl_item_locations b
   WHERE 1 = 1
     AND mq.organization_id = msi.organization_id
     AND ood.organization_id(+) = msi.organization_id
     AND mq.inventory_item_id = msi.inventory_item_id
     AND mq.locator_ID = b.Inventory_location_id
     --AND mq.inventory_item_id = b.Inventory_item_id
     --and mq.organization_id = b.Organization_id
     AND msi.segment1 = nvl(:P_ITEM_CODE,msi.segment1)
     and msi.organization_id=nvl(:p_organization_id,msi.organization_id)  
     and mq.subinventory_code=nvl(:p_subinvcode,mq.subinventory_code)
GROUP BY msi.segment1, ood.organization_name, mq.subinventory_code,msi.organization_id,msi.INVENTORY_ITEM_ID,b.SEGMENT1,b.SEGMENT2,b.SEGMENT3,b.SEGMENT4

============================Lotwise Query===============================

select --*
 moqd.ORGANIZATION_ID
,OOD.ORGANIZATION_NAME
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1 item
,SUM (moqd.primary_transaction_quantity) on_hand
,SUM (moqd.secondary_transaction_quantity) secondary_onhand
,msib.PRIMARY_UOM_CODE
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,(SELECT EXPIRATION_DATE FROM MTL_LOT_NUMBERS WHERE LOT_NUMBER=moqd.LOT_NUMBER
AND inventory_item_id =MOQD.inventory_item_id AND organization_id =MOQD.organization_id )LOT_EXPIRY_DATE
FROM
 mtl_onhand_quantities_detail moqd
,mtl_system_items_b msib
,ORG_ORGANIZATION_DEFINITIONS OOD
   WHERE 1 = 1
     --AND moqd.organization_id = 143
     /*AND moqd.subinventory_code = 'R DRY HALA'
     AND moqd.inventory_item_id = 9095
     AND moqd.owning_organization_id = 143
     AND moqd.planning_organization_id = 143*/
     AND moqd.INVENTORY_ITEM_ID=msib.INVENTORY_ITEM_ID
     AND moqd.organization_id=msib.organization_id
     AND OOD.organization_id=msib.organization_id
group by
moqd.ORGANIZATION_ID
,moqd.SUBINVENTORY_CODE
,msib.SEGMENT1  
,moqd.secondary_uom_code
,moqd.LOT_NUMBER
,msib.PRIMARY_UOM_CODE
,OOD.ORGANIZATION_NAME
,MOQD.inventory_item_id

=================Using API=====================================

create or replace function XXMADN_GET_OHQTY(
p_inv_item_id in varchar2,
p_org_id number,
p_qty_type          IN VARCHAR2
)
return number is

   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
   L_QTY NUMBER;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       msib.inventory_item_id = p_inv_item_id
            AND msib.organization_id = mp.organization_id
            AND msib.organization_id =p_org_id; -- :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number    => 1.0,
      p_init_msg_lst          => 'F',
      x_return_status         => x_return_status,
      x_msg_count             => x_msg_count,
      x_msg_data              => x_msg_data,
      p_organization_id       => v_organization_id,
      p_inventory_item_id     => v_item_id,
      p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control   => FALSE,
      p_is_lot_control        => v_lot_control_code,
      p_is_serial_control     => v_serial_control_code,
      p_revision              => NULL,                          -- p_revision,
      p_lot_number            => NULL,                        -- p_lot_number,
      p_lot_expiration_date   => SYSDATE,
      p_subinventory_code     => NULL,                 -- p_subinventory_code,
      p_locator_id            => NULL,                        -- p_locator_id,
      p_onhand_source         => 3,
      x_qoh                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                  => v_rqoh,         --reservable quantity on-hand
      x_qr                    => v_qr,
      x_qs                    => v_qs,
      x_att                   => v_att,               -- available to transact
      x_atr                   => v_atr                 -- available to reserve
   );

 IF
p_qty_type='OHQ' THEN --On Hand qty
L_QTY :=v_qoh; --v_QuantityOnhand;
ELSE IF
p_qty_type='ATR' THEN --Available to Reserve
L_QTY :=v_atr;
ELSE IF
p_qty_type='ATT' THEN --Available to Transact
L_QTY :=v_att;
END IF;
END IF;
END IF;

 return L_QTY;
 --return v_atr;

   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END XXMADN_GET_OHQTY;

select XXMADN_GET_OHQTY(183645,1404,'OHQ')from dual

Query to find all APPLICATION (module) information

 

-------------------------------------------------------------------------------

-- Query to find all APPLICATION (module) information

-------------------------------------------------------------------------------

SELECT fa.application_id           "Application ID",

       fat.application_name        "Application Name",

       fa.application_short_name   "Application Short Name",

       fa.basepath                 "Basepath"

  FROM fnd_application     fa,

       fnd_application_tl  fat

 WHERE fa.application_id = fat.application_id

   AND fat.language      = USERENV('LANG')

   -- AND fat.application_name = 'Payables'  -- <change it>

 ORDER BY fat.application_name

SQL To Extract the Account Derivation Rules (ADR) In OPM SUbledger Accounting (SLA) (Doc ID 1929297.1)

 Select on ADRs and conditions

1)


select * from xla_seg_rules_b
where segment_rule_code = '&SegmentRuleCode';

'Segment Rule Code' is the value displayed in the 'Rule Code' field in the Accounting Derivation Rules form


2)
select * from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode';


3)
select * from xla_conditions
where segment_rule_detail_id in (select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code =
'&SegmentRuleCode';


4)
select * from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode'));


5)
select * from fnd_flex_value_sets
where flex_value_set_id in
(select flex_value_set_id
from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode')));


6)
select * from fnd_flex_validation_tables
where flex_value_set_id in
(select flex_value_set_id
from xla_sources_b
where (application_id,source_code,source_type_code) in
(select source_application_id,source_code,source_type_code
from xla_conditions
where segment_rule_detail_id in
(select segment_rule_detail_id
from xla_seg_rule_details
where segment_rule_code = '&SegmentRuleCode')));
 

R12 Subledger Accounting (SLA)

What is Subledger Accounting? 

#Subledger Accounting is a Service, not an Application. 

#There are no SLA responsibilities and there is no direct login to SLA. 

# # SLA forms and programs are embedded within standard Oracle Application responsibilities(e.g. Payables Manager). 

=> Simply, It is a rule-based accounting engine, toolset & repository supporting Oracle E-Business Suite modules. 

=> Allows multiple accounting representations for a single business event, resolving conflicts between corporate and local fiscal accounting requirements. 

=> Retains the most granular level of detail in the subledger accounting model, with different summarization options in the General Ledger, allowing full auditability and reconciliation. 

=> Introduces a common data model and UI across subledgers, replaces various disparate 11i setups, providing single source of truth for financial and management analysis. Screen shots of sample Invoice Distribution and It's Accounting Journal Entries 

Simple Illustration on Journal Line Creation in SLA 

Please go thorough the AMB components explained below to understand more about this picture.

What is Accounting Methods Builder(AMB) in SLA? 

A set of screens which provides flexibility to create your own subledger accounting set up or use seeded subledger accounting setup. 

You can use the AMB to define the way in which subledger transactions are accounted. This enables you to create and modify subledger journal line setups and application accounting definitions. These definitions define the journal entries that enable an organization to meet specific fiscal, regulatory, and analytical requirements. These definitions are grouped into subledger accounting methods and assigned collectively to the ledger. 

Following picture shows hierarchy of the components in the AMB. 

 

# Each ledger is assigned with SLAM. 

# Subledger accounting method(SLAM) which tells what type of accounting method you are using(Ex: Cash or Accrual) in your ledger. 

# Under SLAM, you will find set of Application Accounting Definitions(AAD) for different subledgers(Ex: Payables, Receivables) 

# Under AAD, you will find set of journal line definitions(JLD) for each Event Class(Ex: Invoices, Prepayments, Payments) and Event Type(Ex: Invoice Validated, Prepay Application, Payment Created) combination. 

# Each journal line definitions(JLD) holds group of JLT's(Ex: Name it Liability, Item Expense, Gain, Loss, etc), ADR(rules), JED(description) for each Event Class and Event Type combination. 

# Journal Line Types(JLT) contains.. 

1. Accounting Attributes(Ex: Accounting Date, Entered Amount, Accounted Amount, Party Id, etc ) 

2. Basic Info which determines journal line properties( Ex: Side(Cr or Dr), Balance Type(Actual or Encum), TransferToGL(Summary or Detail), etc) 

3. Conditions(This condition need to be satisfied to use this JLT) 

# Account Derivation Rules(ADR) (Ex: Accounting segment values. It is nothing but GL account) 

# Journal Entry Descriptions(JED): Which give more information about transaction (Ex: Invoice/Check details) 

Navigation Paths to SLA Forms

Explain AMB Components? 

Event Model(Definition of the subledger transaction types and lifecycle) 

Event Entities 

Group event classes into technical transaction models called event entities. For example, group the event classes Invoices and Prepayments into the event entity Invoices because both classes of transaction are stored in the Payables invoice transaction table (AP_INVOICES_ALL). Event entities enable you to treat events for a single transaction model in the same way. The event entity often logically corresponds to a single document used as a basis for several related transactions. 

Event Class 

Group accounting event types into user-orientated transaction categories called event classes. For example, group the event types Invoice Approved, Invoice Adjusted, and Invoice Canceled into the event class Invoices. Then assign AMB components, such as journal line types, by event class within the application accounting definition. This assignment simplifies setup when the accounting requirements for all event types in a class are the same. Also, sources assigned to an event class are available for the accounting of all event types in that event class. 

Example Payables: 

Invoice, Debit Memo, Prepayment, Payments, Refunds 

Receivables: Invoice, Deposit, Receipt, Bill Receivable 

Event Type 

Each accounting event should be represented by an accounting event type. These types are registered in the AMB. When subledger journal entries need to be created, the event type determines which application accounting definitions should be used to process the accounting event. Application accounting definitions created in the AMB determine the lines, descriptions, accounts, and other elements of subledger journal entries.

Example 

AP Invoice Events: Validated, Adjusted, Cancelled 

AR Receipt Events: Created, Applied, Unapplied, Updated, Reversed 

Subledger Accounting Method(SLAM) 

The subledger accounting method(SLAM) is a collection of accounting definitions for all the applications that you will be generating accounting for. Each primary and subledger level or adjustment secondary ledger is associated with a SLAM, which determines the accounting rules and standards that will be applied when generating entries for that ledger. 

Example: 

Standard Accrual, Standard Cash, etc

Application Accounting Definition(AAD) 

Use Application Accounting Definitions(AADs) to assign journal line definitions and header descriptions to event classes and event types. AADs must be included in a subledger accounting method and assigned to a ledger. You can group accounting definitions from multiple products, such as Oracle Payables, receivables Assets into a single accounting method. 

Journal Line Definition(JLD) 

Journal line type, description, account derivations rules grouped together as a journal line definition to create the rule for particular event type.

Journal Line Type(JLT) 

-Identify the natural side: Debit, Credit, Gain/Loss 

-Determine the accounting class 

-Set under which conditions the rule will create a line 

-Define the values needed for entry line generation, such as amount, currency, conversion rate information 

-Control behavior for certain features i.e. multi period accounting, business flows, line merging and summarization 

Account Derivation Rules(ADR) 

Account derivation rules are used to determine the account combinations for subledger journal entries. You can define various rules in te AMB to determine how a journal entry account is derived. You can derive accounts segment by segment or as a complete account combination. This picture shows an Account Derivation Rule with conditional logic. If the condition holds for priority 1, then this source (Invoice Liability Account) is used. If not, SLA uses the source for priority 2(If it is available). 

Journal Entry Description 

This is useful in finding the actual transaction object details(Ex: Invoice/Payment details from journal line) 

Transaction Object 

Example for transaction objects: 

ap_invoice_extract_details_v.xdf 

ap_invoice_extract_header_v.xdf 

ap_payment_extract_details_v.xdf 

ap_payment_extract_header_v.xdf 

ap_prepayapp_extract_details_v.xdf 

ap_system_parameters_extract_v.xdf 

Transaction Object is nothing but a view which fetches all transaction information required to create journal line for particular event class. AP_INVOICE_EXTRACT_HEADER_V, AP_INVOICE_EXTRACT_DETAILS_V are transaction objects for event class Invoices. So, accounting for all invoice type events get transaction information from these transaction objects.

Sources Each column in the transaction object is defined as Source in the AMB. AMB uses these sources to get transaction information from Transaction Objects

Accounting Attributes 

Sources are mapped with Accounting Attributes. Accounting Attributes are bridge between JLT and Sources. 

Example 

GL Date, Entered Currency Code, Entered Amount, Accounted Amount, Conversion Rate Date, Conversion Rate Type, Conversion Rate, Distribution Type, Party Type, Party Identifier, Party Site Identifier

What are the important tables in SLAAccounting? 

The XLA_EVENTS table stores records for accounting events generated by subledger applications. Each product team populates this table by calling Subledger Accounting API and the respective product team will decide when this table is to be populated during the transaction life cycle. 

The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers. 

The XLA_DISTRIBUTION_LINKS table stores detailed distributions for journal entries. This table stores the data at most granular level and represents data contained in respective subledger product’s distribution tables. The detailed distributions stored in this table are merged into accounting lines and stored in XLA_AE_LINES table. Subledger Accounting uses this table for processing reversals and business flows. 

The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines. This table will store at least one row for debit and one row for credit for each accounting entry created. If multiple debit or credit journal entry lines exists for any specific event type and if the journal line type allows merge matching lines then these lines will be merged into single line. The unmerged granular level of detail for each accounting line will be available in XLA_DISTRIBUTION_LINKS table. 

What are the Accounting Methods seeded in SLA? 

Standard Accrual 

Standard Cash 

Encumbrance Accrual and Encumbrance Cash 

United States Federal 

China Standard Accrual 

What are the reports available in SLA? 

Journal Entries Report 

Account Analysis Report 

Third Party Balances Report 

Period Close Exceptions Report 

Open Account Balances Listing 

Period Moving Average Cost(PMAC)