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 create org id by using api Oracle apps R12

--------------API TO CREATE BUSINESS GROUP IN BACK END.
CREATE OR REPLACE package body APPS.test_pkg1 is

procedure create_org(errbuff out varchar2,retcode out number) is
--Cursor to fetch the information stored inside the XX_ORG TABLE
cursor org_info is
select org.org_name,bg_name,loc,dt_start,dt_end
from
xx_org org;
 --Cursor to fetch the information stored inside the
        --hr_all_organization_units by passing the name of the business group.
cursor org_bg(cp_bg_name varchar2) is
select organization_id,date_from from hr_all_organization_units where name=cp_bg_name;

ln_bg_id number default null;
ld_bg_stdt date default sysdate;
lv_succ_status  varchar2(10) default 'SUCCESS';
lv_error_status  varchar2(10) default 'ERROR';
lv_error_msg    varchar2(500) default null;
lv_status      varchar2(10);
ln_org_id       number default 0;
ln_obj_ver_no   number default 0;
lb_org_warning  boolean default false;
begin
fnd_file.put_line(1,'Begin Process...');
--Opening the cursor Org_info to fetch the records into org_rec variable
for org_rec in org_info
loop
lv_error_msg:=null;
ln_org_id:=0;
ln_obj_ver_no:=0;
lb_org_warning:=false;
ln_bg_id:=null;
ld_bg_stdt:=sysdate;
lv_status:=lv_succ_status;
fnd_file.put_line(1,'Begin creating organization : '||org_rec.org_name);

 --Opening the cursor org_bg by passing the business group name from the above cursor variable.
   open org_bg(cp_bg_name => org_rec.bg_name);
     --Fetching the records into two different variables from the above cursor Org_info
        fetch org_bg into ln_bg_id,ld_bg_stdt;
        -- Close the cursor org_bg
   close org_bg;
fnd_file.put_line(1,'               BG ID : '||ln_bg_id);
fnd_file.put_line(1,'               BG start dt : '||to_char(ld_bg_stdt,'dd-mon-yyyy'));     
fnd_file.put_line(1,'               org start dt : '||to_char(org_rec.dt_start,'dd-mon-yyyy'));
fnd_file.put_line(1,'              1 error status : '||lv_status);

 --Passing the variable Business Group ID and verifying that whether it is an existing Business Group or Not.
         --If the Business Group Id is NULL then it will enter into the if condition and
         --updates the error msgs in the pqr_org_load table.


   if ln_bg_id is null then
        lv_status:=lv_error_status;
        lv_error_msg:='Business group does not exists';
   end if;
fnd_file.put_line(1,'             2 error status : '||lv_status);


--Passing the variable Business Group Start Date and verifying that whether it is greater then the
--Organization start date or not.

        if ld_bg_stdt > org_rec.dt_start then
        lv_status:=lv_error_status;
        lv_error_msg:=lv_error_msg||' Business group start date is after org start date';
   end if;
   fnd_file.put_line(1,'             3 error status : '||lv_status);
 
--If the above condition is satisfied then we will create the Organization for that particular Business Group.
   
   if lv_status<>lv_error_status then
 
   hr_organization_api.create_organization( p_validate                  => false
                                           ,p_effective_date            => sysdate
                                           ,p_business_group_id         => ln_bg_id
                                           ,p_date_from                 => org_rec.dt_start
                                           ,p_name                      => org_rec.org_name
                                           ,p_organization_id           => ln_org_id
                                           ,p_object_version_number     => ln_obj_ver_no
                                           ,p_duplicate_org_warning     => lb_org_warning);
fnd_file.put_line(1,'               created Organization : '||ln_org_id);                                           
        update xx_org set status=lv_succ_status,error_msg='Organization '||org_rec.org_name||' created with org id : '||ln_org_id
        where org_name=org_rec.org_name;
                                             
   else
        update xx_org set status=lv_error_status,error_msg=lv_error_msg
        where org_name=org_rec.org_name;
fnd_file.put_line(1,' Error message  : '||lv_error_msg);           
   end if;
 fnd_file.put_line(1,'Completion of org creation : '||org_rec.org_name);     

end loop;
commit;
exception
when others then
fnd_file.put_line(1,'exception occured '||SQLERRM);


end create_org;
end test_pkg1;
/

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

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

How to delete the records from a table by using bulk collect and forall ?

Hi, In this article We are going to know how to delete the records by using bulk binds(Bulk collect & Forall).


CREATE OR REPLACE PACKAGE XX_BULK_RECORDS_DELETE_PKG
IS

PROCEDURE XX_BULK_DELETE;

END XX_BULK_RECORDS_DELETE_PKG;

/
SHO ERRORS
/


CREATE OR REPLACE PACKAGE BODY XX_BULK_RECORDS_DELETE_PKG
IS

PROCEDURE XXPC_BULK_DELETE
IS
-- +====================================================================+
-- | lcu_mtl_trxns cursor is used to get the transactions from
-- | mtl_material_transactions which are not having consted_flag as 'Y'
-- +====================================================================+
CURSOR  lcu_mtl_trxns
IS
SELECT  transaction_id
FROM    mtl_material_transactions_bkup
WHERE   costed_flag <> 'Y'
AND     organization_id = 889;

-- +====================================================================+
-- |Declaring Local Variables.                                       
-- +====================================================================+
TYPE bulk_rec IS TABLE OF lcu_mtl_trxns%rowtype;
bulk_tab    bulk_rec;
bulk_errors NUMBER;
dml_errors  EXCEPTION;
PRAGMA exception_init(dml_errors,-24381);

BEGIN

OPEN lcu_mtl_trxns;
LOOP
FETCH lcu_mtl_trxns BULK COLLECT INTO bulk_tab LIMIT 100;

FORALL indx IN bulk_tab.FIRST .. bulk_tab.LAST SAVE EXCEPTIONS
DELETE FROM mtl_material_transactions_bkup
WHERE transaction_id = bulk_tab(indx).transaction_id;
EXIT WHEN lcu_mtl_trxns%notfound;

END LOOP;
CLOSE lcu_mtl_trxns;

COMMIT;

EXCEPTION
WHEN dml_errors THEN
bulk_errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('number of statements failed are '||bulk_errors);
FOR ind IN 1..bulk_errors LOOP
dbms_output.put_line('error #'||ind|| ' is occured during '|| 'iterations #' ||sql%bulk_exceptions(ind).error_index);
dbms_output.put_line('error  message is ' || SQLERRM(-sql%bulk_exceptions(ind).error_code));

end loop;

WHEN OTHERS THEN
dbms_output.put_line('Err is: '||SQLCODE ||' , '||SQLERRM);
END XX_BULK_DELETE;

END XX_BULK_RECORDS_DELETE_PKG;
/
SHO ERRORS
/
EXEC XX_BULK_RECORDS_DELETE_PKG.XXPC_BULK_DELETE;

select * from mtl_material_transactions_bkup;

How to find the receipt details based on requisition ?

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; 

How to find the receipt details based on PO number ?

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;