How to find the receipt details based on PO number in the Oracle Apps R12

This query is used to find the receipt details based on PO number.

SELECT  prh.segment1 "Req Number"

       ,prh.requisition_header_id

       ,prh.org_id "Req orgid"

       ,prh.creation_date

       ,prl.requisition_line_id

       ,prl.line_num

       ,prl.item_description "req line description"

       ,prd.distribution_id

       ,prd.set_of_books_id

       ,pda.po_distribution_id

       ,pla.po_line_id

       ,pla.line_num "Po Linenum"

       ,pha.segment1 "Po Number"

       ,pha.po_header_id

       ,as1.segment1 "Vendor Number"

       ,as1.vendor_name

       ,rt.TRANSACTION_TYPE

       ,rsl.shipment_line_id

       ,rsl.line_num "Shipment Linenum"

       ,rsl.shipment_line_status_code

       ,rsl.item_id

       ,rsl.source_document_code

       ,rsl.from_organization_id

       ,rsl.to_organization_id

       ,rsl.to_subinventory

       ,rsl.quantity_shipped

       ,rsl.quantity_received

       ,rsl.deliver_to_person_id

       ,rsl.item_description "Shipment lines Description"

       ,rsl.unit_of_measure

       ,rsh.receipt_num

       ,rsh.shipment_num

 

FROM    po_requisition_headers_All prh

       ,po_requisition_lines_All prl

       ,po_req_distributions_All prd

       ,po_distributions_all pda

       ,po_lines_all pla

       ,po_headers_all pha

       ,ap_suppliers as1

       ,rcv_transactions rt

       ,rcv_shipment_lines rsl

       ,rcv_shipment_headers rsh

 

WHERE 1=1

-- AND   prh.segment1              =  '143'

AND   prh.org_id                = 791

AND   prh.requisition_header_id = prl.requisition_header_id

AND   prl.requisition_line_id   = prd.requisition_line_id

AND   prd.distribution_id       = pda.req_distribution_id(+)

AND   pla.po_line_id            = pda.po_line_id       

AND   pha.po_header_id          = pla.po_header_id       

AND   pha.org_id                = 791                   

AND   pha.type_lookup_code      NOT IN ('RFQ','QUOTATION')

AND   pha.segment1 = '102'

AND   pha.vendor_id             = as1.vendor_id           

AND   pha.po_header_id          = rt.po_header_id         

AND   pla.po_line_id            = rt.po_line_id           

AND   pda.po_distribution_id    = rt.po_distribution_id   

AND   rt.organization_id        = 791                     

-- AND   prl.requisition_line_id    = rt.requisition_line_id(+)

AND  rt.shipment_line_id        = rsl.shipment_line_id   

AND  pha.po_header_id           = rsl.po_header_id         

AND  pla.po_line_id             = rsl.po_line_id           

AND  pda.po_distribution_id     = rsl.po_distribution_id     

AND  prd.distribution_id        = rsl.req_distribution_id   

-- AND  prl.requisition_line_id     = rsl.requisition_line_id(+)

AND  rsh.shipment_header_id     = rsl.shipment_header_id;  

How to find the receipt details based on requisition in the Oracle Apps R12

This query is used to find the receipt details based on requisition.

SELECT  prh.segment1 "Req Number"

       ,prh.requisition_header_id

       ,prh.org_id "Req orgid"

       ,prh.creation_date

       ,prl.requisition_line_id

       ,prl.line_num

       ,prl.item_description "req line description"

       ,prd.distribution_id

       ,prd.set_of_books_id

       ,pda.po_distribution_id

       ,pla.po_line_id

       ,pla.line_num "Po Linenum"

       ,pha.segment1 "Po Number"

       ,pha.po_header_id

       ,as1.segment1 "Vendor Number"

       ,as1.vendor_name

       ,rt.TRANSACTION_TYPE

       ,rsl.shipment_line_id

       ,rsl.line_num "Shipment Linenum"

       ,rsl.shipment_line_status_code

       ,rsl.item_id

       ,rsl.source_document_code

       ,rsl.from_organization_id

       ,rsl.to_organization_id

       ,rsl.to_subinventory

       ,rsl.quantity_shipped

       ,rsl.quantity_received

       ,rsl.deliver_to_person_id

       ,rsl.item_description "Shipment lines Description"

       ,rsl.unit_of_measure

       ,rsh.receipt_num

       ,rsh.shipment_num

   

FROM    po_requisition_headers_All prh

       ,po_requisition_lines_All prl

       ,po_req_distributions_All prd

       ,po_distributions_all pda

       ,po_lines_all pla

       ,po_headers_all pha

       ,ap_suppliers as1

       ,rcv_transactions rt

       ,rcv_shipment_lines rsl

       ,rcv_shipment_headers rsh

   

WHERE 1=1

AND   prh.segment1              =  '143'

AND   prh.org_id                = 791

AND   prh.requisition_header_id = prl.requisition_header_id

AND   prl.requisition_line_id   = prd.requisition_line_id

AND   prd.distribution_id       = pda.req_distribution_id(+)

AND   pla.po_line_id            = pda.po_line_id         

AND   pha.po_header_id          = pla.po_header_id         

AND   pha.org_id                = 791                     

AND   pha.type_lookup_code      NOT IN ('RFQ','QUOTATION')

-- AND   pha.segment1 = '102'

AND   pha.vendor_id             = as1.vendor_id             

AND   pha.po_header_id          = rt.po_header_id           

AND   pla.po_line_id            = rt.po_line_id             

AND   pda.po_distribution_id    = rt.po_distribution_id     

AND   rt.organization_id        = 791                       

-- AND   prl.requisition_line_id    = rt.requisition_line_id(+)

AND  rt.shipment_line_id        = rsl.shipment_line_id     

AND  pha.po_header_id           = rsl.po_header_id           

AND  pla.po_line_id             = rsl.po_line_id             

AND  pda.po_distribution_id     = rsl.po_distribution_id       

AND  prd.distribution_id        = rsl.req_distribution_id     

-- AND  prl.requisition_line_id     = rsl.requisition_line_id(+)

AND  rsh.shipment_header_id     = rsl.shipment_header_id; 

Supplier certification report in R12

In front end we can see the supplier certifications by following the below navigation

Navigation : Supplier Lifecycle Management –> Search for Supplier "%XX%" –> Click on the            Organization tab -> Click on Supplier Certification Tab

Query:

--------

SELECT  PSPE.PARTY_ID    PARTY_ID

       ,PSPE.C_EXT_ATTR1 CERTIFICATE

       ,PSPE.C_EXT_ATTR2 CERTIFICATE_NUMBER

       ,to_char(to_date(PSPE.D_EXT_ATTR3,'DD-MM-YY'),'DD-MON-YYYY') VALID_FROM

       ,to_char(to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY'),'DD-MON-YYYY') VALID_THROUGH

       ,to_char(to_date(PSPE.D_EXT_ATTR5,'DD-MM-YY'),'DD-MON-YYYY') LAST_VALIDATED

       ,PSPE.REQUEST_ID  REQUEST_ID

       ,AS1.VENDOR_NAME  VENDOR_NAME

       ,AS1.SEGMENT1     VENDOR_NUMBER

FROM POS_SUPP_PROF_EXT_B PSPE

    ,AP_SUPPLIERS AS1

WHERE 1 = 1

  AND PSPE.attr_group_id=221

  AND to_date(PSPE.D_EXT_ATTR4,'DD-MM-YY') BETWEEN SYSDATE and SYSDATE+:P_EXP_DAYS

  AND AS1.PARTY_ID = PSPE.PARTY_ID

ORDER BY TO_DATE(PSPE.D_EXT_ATTR4,'DD-MM-YY')

        ,AS1.VENDOR_NAME

How to take backup of PLSQL objects programatically in Oracle Apps R12

This is package is used to take the backup of the PLSQL objects.

create or replace PACKAGE XX_WRITE_FILES_PKG

IS

PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);

PROCEDURE MAIN;

END XX_WRITE_FILES_PKG;

/

SHO ERRORS

/

create or replace PACKAGE BODY XX_WRITE_FILES_PKG

IS

-- |                                                                             |

-- |Description      : XX_WRITE_FILES_PKG is used to take the backup of          |

-- |                   Database objects like PROCEDURE,PACKAGE BODY,PACKAGE      |

-- |                   TYPE BODY,TRIGGER,FUNCTION,TYPE.                          |

PROCEDURE Debug( p_message  IN  VARCHAR2

               ) IS

lv_message       VARCHAR2(200);

BEGIN

      lv_message    := SUBSTR(p_message,1,240);

       fnd_file.put_line(fnd_file.log, lv_message);

   -- dbms_output.put_line(lv_message);

END Debug;

-- +====================================================================+

-- | Name             : write_out                                       |

-- | Description      : To write to the Output file of a concurrent Prog|

-- | Parameters       : pv_mesg            - Message String             |

-- |                                                                    |

-- +====================================================================+

PROCEDURE write_out(pv_mesg  IN  VARCHAR2) IS

BEGIN

    FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));

   --  dbms_output.put_line(substr(pv_mesg,1,500));

END write_out;

PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)

IS

CURSOR lcu_file_name (cv_object_name VARCHAR2)

IS

SELECT text

FROM   user_source

WHERE NAME = cv_object_name;

l_file  UTL_FILE.FILE_TYPE;

BEGIN

Debug('XX_WRITE_FILES_PRC => Begining of Procedure');

l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');

Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');

FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP

UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);

END LOOP;

Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');

UTL_FILE.FCLOSE(l_file);

Debug('XX_WRITE_FILES_PRC => End of procedure');

EXCEPTION

WHEN OTHERS THEN

Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);

Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);

END XX_WRITE_FILES_PRC;

PROCEDURE MAIN (ERRBUF  OUT VARCHAR2

               ,RETCODE OUT VARCHAR2)

IS

CURSOR lcu_object_name

IS

SELECT  xbon.OBJECT_NAME

FROM    XX_BKUP_OBJECT_NAMES xbon;

BEGIN

Debug('MAIN => Begining of PROCEDURE');

FOR lr_object_name IN lcu_object_name LOOP

Debug('MAIN => Entered into for-loop lr_object_name');

XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);

write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);

END LOOP;

Debug('MAIN => End of PROCEDURE');

EXCEPTION

WHEN OTHERS THEN

Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);

END MAIN;

END XX_WRITE_FILES_PKG;

/

SHO ERRORS

/

Vendor creation by using API in Orale Apps R12

DECLARE

   l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;

   l_return_status   VARCHAR2(10);

   l_msg_count       NUMBER;

   l_msg_data         VARCHAR2(1000);

   l_vendor_id        NUMBER;

   l_party_id           NUMBER;

   cursor c1 is select * from xx_sup_stage;

BEGIN

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

   -- Required

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

   for i in c1 loop

   l_vendor_rec.VENDOR_ID:= i.VENDOR_ID;

   l_vendor_rec.VENDOR_NAME:= i.VENDOR_NAME;

   l_vendor_rec.VENDOR_NAME_ALT:=i.VENDOR_NAME_ALT;

   l_vendor_rec.SEGMENT1:= i.SEGMENT1;

   l_vendor_rec.SUMMARY_FLAG:=i.SUMMARY_FLAG;

   l_vendor_rec.ENABLED_FLAG:=i.ENABLED_FLAG;

   l_vendor_rec.TERMS_ID:=i.TERMS_ID;

   l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:=i.PAY_DATE_BASIS_LOOKUP_CODE;

   l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_LOOKUP_CODE;

   l_vendor_rec.INVOICE_CURRENCY_CODE:=i.INVOICE_CURRENCY_CODE;

   l_vendor_rec.PAYMENT_CURRENCY_CODE:=i.PAYMENT_CURRENCY_CODE;

   l_vendor_rec.START_DATE_ACTIVE:=i.START_DATE_ACTIVE;

 

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

   -- Optional

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

   l_vendor_rec.match_option  :='R';

 

   pos_vendor_pub_pkg.create_vendor

   (    -- -------------------------

        -- Input Parameters

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

        p_vendor_rec      => l_vendor_rec,

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

        -- Output Parameters

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

        x_return_status   => l_return_status,

        x_msg_count       => l_msg_count,

        x_msg_data         => l_msg_data,

        x_vendor_id        => l_vendor_id,

        x_party_id           => l_party_id

   );

 

   IF l_return_status ='S' THEN

  -- Update vendor id in stage tables through autonomus prrogram.

 

   ELSE

   -- Update vendor id in stage tables through autonomus prrogram.

  End if;

 

  end loop;

 

  commit;

 

EXCEPTION

      WHEN OTHERS THEN

                   ROLLBACK;

                   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Sales order query in Oracle Apps R12

--Query used to display the Order details and its invoice details

select ooh.ORDER_NUMBER     "Order Number"

      , ra.CUSTOMER_NUMBER  "Customer Number"

      , ra.CUSTOMER_NAME    "Customer Name"

      ,wnd.SOURCE_HEADER_ID "Delivery Number"

      ,ool.LINE_ID          "Order LineID"

      ,ool.LINE_NUMBER      "Order Line Number"

      ,rct.TRX_NUMBER       "Invoice Number"

      ,rct.TRX_DATE         "Invoice Date"

      ,ool.ORDERED_QUANTITY "Ordered Quantity"

      ,ool.SHIPPED_QUANTITY "Shipped Quantity"

     ,rctl.QUANTITY_INVOICED "Invoiced Quantity"

     ,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE "Ordered Amount"

     ,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE "Invoiced Amount"

     ,(select sum(l.ORDERED_QUANTITY*l.UNIT_SELLING_PRICE)

      from oe_order_headers_all h

          ,oe_order_lines_all l

      where h.HEADER_ID in l.HEADER_ID

         and h.HEADER_ID=ooh.HEADER_ID

      group by h.HEADER_ID ) "Sub Total"

     from oe_order_headers_all ooh

    ,oe_order_lines_all ool

    ,ra_customers ra

    ,wsh_new_deliveries wnd

    ,ra_customer_trx_all rct

    ,ra_customer_trx_lines_all rctl

where 1=1

   and ooh.ORDER_NUMBER     = '66415'

   and ooh.HEADER_ID        = ool.HEADER_ID

   and  ra.CUSTOMER_ID      = ooh.SOLD_TO_ORG_ID

   and ooh.HEADER_ID        = wnd.SOURCE_HEADER_ID(+)

   --and ra.CUSTOMER_ID       = wnd.CUSTOMER_ID

   and to_char(ooh.ORDER_NUMBER)     = rct.CT_REFERENCE

   and rct.SOLD_TO_CUSTOMER_ID       =  ra.CUSTOMER_ID

   and rct.CUSTOMER_TRX_ID           = rctl.CUSTOMER_TRX_ID

   and rctl.SHIP_TO_CUSTOMER_ID      = ra.CUSTOMER_ID

group by ooh.HEADER_ID

      ,ooh.ORDER_NUMBER    

      , ra.CUSTOMER_NUMBER  

      , ra.CUSTOMER_NAME    

      ,wnd.SOURCE_HEADER_ID 

      ,ool.LINE_ID          

      ,ool.LINE_NUMBER      

      ,rct.TRX_NUMBER       

      ,rct.TRX_DATE         

      ,ool.ORDERED_QUANTITY 

      ,ool.SHIPPED_QUANTITY 

     ,rctl.QUANTITY_INVOICED 

     ,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE 

     ,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE

How to find the RMA order receipt details in Oracle Apps R12

SELECT ooha.ORDER_NUMBER "SALES ORDER"

              ,ooha.ORDER_CATEGORY_CODE

              ,oola.ORDERED_ITEM

              ,oola.SUBINVENTORY

              ,rsh.SHIPMENT_NUM

              ,rsh.RECEIPT_NUM

              ,rsh.CUSTOMER_ID

              ,rsl.UNIT_OF_MEASURE

              ,rsl.ITEM_DESCRIPTION

              ,rsl.SHIPMENT_LINE_STATUS_CODE

              ,rsl.SOURCE_DOCUMENT_CODE

FROM OE_ORDER_HEADERS_ALL ooha

           ,OE_ORDER_LINES_ALL oola

           ,RCV_SHIPMENT_HEADERS rsh

           ,RCV_SHIPMENT_LINES rsl

WHERE  1=1

AND ooha.header_id                                = oola.header_id

AND ooha.header_id                                = rsl.OE_ORDER_HEADER_ID

AND rsh.shipment_header_id                  = rsl.shipment_header_id

AND rsl.OE_ORDER_LINE_ID                = o ola.line_id 

AND ooha.ORDER_NUMBER                 = '56' 

AND rsl.SOURCE_DOCUMENT_CODE = 'RMA';

Payables invoice prepayment query in Oracle Apps R12

SELECT   pv.vendor_name C_vendor_name,

         pvs.address_line1 C_address_line1,

         pvs.address_line2 C_address_line2,

         pvs.address_line3 C_address_line3,

            DECODE (pvs.city, '', '', pvs.city || ', ')

         || DECODE (pvs.state, '', '', pvs.state || ' ')

         || pvs.zip

            C_city_state_zip,

         pvs.country C_country,

         aipp.last_update_date C_application_date,

         aipp.prepayment_amount_applied C_amount_applied,

         inv.invoice_currency_code C_currency_code,

         pp.invoice_num C_prepay_num,

         inv.invoice_num C_invoice_num,

         NVL (inv.invoice_amount, 0) - NVL (inv.amount_paid, 0)

            C_amt_remaining

  FROM   ap_suppliers pv,

         ap_supplier_sites_all pvs,

         ap_invoices_all inv,

         ap_invoices_all pp,

         ap_invoice_prepays_all aipp

 WHERE       aipp.invoice_id = inv.invoice_id

         AND aipp.prepay_id = pp.invoice_id

         AND inv.vendor_id = pp.vendor_id

         AND inv.vendor_id = pv.vendor_id

         AND pv.vendor_id = pvs.vendor_id

         AND pvs.vendor_site_id = inv.vendor_site_id

         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

         AND aipp.last_update_date >= &InvDate

UNION

SELECT   pv.vendor_name C_vendor_name,

         pvs.address_line1 C_address_line1,

         pvs.address_line2 C_address_line2,

         pvs.address_line3 C_address_line3,

            DECODE (pvs.city, '', '', pvs.city || ', ')

         || DECODE (pvs.state, '', '', pvs.state || ' ')

         || pvs.zip

            C_city_state_zip,

         pvs.country C_country,

         aid2.last_update_date C_application_date,

         NVL (

            ap_invoices_utility_pkg.get_pp_amt_applied_on_date (

               inv.invoice_id,

               pp.invoice_id,

               aid2.last_update_date

            ),

            0

         )

            C_amount_applied,

         inv.invoice_currency_code C_currency_code,

         pp.invoice_num C_prepay_num,

         inv.invoice_num C_invoice_num,

         NVL (inv.invoice_amount, 0)

         - (ap_invoices_pkg.get_prepaid_amount (inv.invoice_id))

            C_amt_remaining

  FROM   ap_suppliers pv,

         ap_supplier_sites_all pvs,

         ap_invoices_all inv,

         ap_invoices_all pp,

         ap_invoice_distributions_all aid1,

         ap_invoice_distributions_all aid2

 WHERE       aid1.invoice_id = inv.invoice_id

         AND aid2.invoice_id = pp.invoice_id

         AND aid2.invoice_distribution_id = aid1.prepay_distribution_id

         AND aid1.line_type_lookup_code = 'PREPAY'

         AND inv.vendor_id = pp.vendor_id

         AND inv.vendor_id = pv.vendor_id

         AND pv.vendor_id = pvs.vendor_id

         AND pvs.vendor_site_id = inv.vendor_site_id

         AND NVL (aid1.reversal_flag, 'N') != 'Y'

         AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

         AND inv.invoice_date >= &InvDat