Apps Initialize / fnd log procedures

 CREATE OR REPLACE PROCEDURE APPS."XXX_LOG" (pmessage VARCHAR2)

 AS

 BEGIN

 -- This Procedure is used to write on Log file

 DBMS_OUTPUT.put_line (pmessage);

 fnd_file.put_line (fnd_file.LOG, pmessage);

 EXCEPTION

 WHEN OTHERS

 THEN

 NULL;

 END XXX_LOG;

PROCEDURE apps_initialize (p_org_id IN NUMBER)

    IS

        v_user_id        NUMBER := fnd_profile.VALUE ('USER_ID');

        v_resp_id        NUMBER := fnd_profile.VALUE ('RESP_ID');

        v_resp_appl_id   NUMBER := fnd_profile.VALUE ('RESP_APPL_ID');

        lv_request_id    NUMBER;

        lv_org_id        NUMBER := fnd_profile.VALUE ('ORG_ID');

    --

    BEGIN

        --

        lv_org_id := p_org_id;

        --

        IF (lv_org_id = 777)                                         

        THEN

            v_user_id := 888;

            v_resp_id := 53223;

            v_resp_appl_id := 200;

        ELSIF (lv_org_id = 9874)                                         

        THEN

            v_user_id := 99;

            v_resp_id := 53345;

            v_resp_appl_id := 200;

        ELSIF (lv_org_id = 3657)                                         

        THEN

            v_user_id := 666;

            v_resp_id := 53885;

            v_resp_appl_id := 200;

        ELSE

            v_user_id := 33;

            v_resp_id := 50645;

            v_resp_appl_id := 200;

        END IF;

        fnd_global.apps_initialize (user_id        => v_user_id,

                                    resp_id        => v_resp_id,

                                    resp_appl_id   => v_resp_appl_id);

        mo_global.init ('SQLAP');

        fnd_client_info.set_org_context (lv_org_id);

    END apps_initialize;

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 PR PO Receipt Invoice Payments

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