Query PO with IL tax

SELECT distinct pha.segment1 "Po Number",
       pha.creation_date "Date",
       pha.approved_date "Approved Date",
       (pla.unit_price* pla.quantity) "Amount w/o Tax",
       jpt.tax_amount "Tax Amount",
       jpll.total_amount "Total Amount",
       pla.line_num "Line Num",
       pv.vendor_name"Vendor Name",
       jrt.tax_name"Tax Name",
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
       hou.name "Organization Name"
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_vendors pv,
       JAI_PO_LINE_LOCATIONS jpll,
       hr_operating_units hou,
       JAI_RCV_TAX_V jrt,
       jai_po_taxes jpt,
       PO_LOOKUP_CODES POLC1,
       PO_LOOKUP_CODES POLC2
 WHERE pha.po_header_id=pla.po_header_id
   AND plla.po_header_id=pha.po_header_id
   AND pv.vendor_id=pha.vendor_id
   AND plla.po_line_id=pla.po_line_id
   AND pha.segment1=:P_PONUM   --'2012102020109'
   AND jpll.po_header_id=pha.po_header_id
   AND jpll.po_line_id=plla.po_line_id
   AND pha.org_id= hou.organization_id
   AND jrt.vendor_id=pha.vendor_id
   AND jrt.tax_id= jpt.tax_id
   AND jpt.po_header_id=pha.po_header_id
   AND jpt.po_line_id=pla.po_line_id
   AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
   AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
   AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
   AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
   AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
   AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
       pha.creation_date,
       pha.approved_date,
       jpt.tax_amount ,
       jpll.total_amount,
       pla.line_num ,
       pv.vendor_name,
       jrt.tax_name,
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
       hou.name*/

Query Project Unbilled Amount

SELECT TO_CHAR(TRUNC(PE.CREATION_DATE),'MON-YYYY') MONTH,
    (SELECT NAME FROM HR_ORGANIZATION_UNITS
          WHERE organization_id =ppa.carrying_out_organization_id ) Business_unit,
       PPA.SEGMENT1 PROJECT_NUMBER,
      PPA.NAME PROJECT_NAME,
      PPC.CUSTOMER_NAME CUSTOMER_NAME,
     XX_GET_AGREEMENT_NUM(ppa.project_id) AGREEMENT_NUMBER,
     (CASE WHEN ppc.customer_id in (4046,12040,5040,5044) THEN 'Internal'
      ELSE 'External'        
      END) INTERNAL_OR_EXTERNAL,
     -- NULL COST_CENTER,
      PE.DESCRIPTION,
     PE.BILL_TRANS_BILL_AMOUNT UNBILLED_AMOUNT
      -- PE.PROJECT_ID,
    -- PE.EVENT_TYPE,
    -- PE.BILLED_FLAG,
    -- PE.DESCRIPTION
FROM   PA_EVENT_TYPES pet,
      PA_EVENTS PE,
      PA_PROJECTS_ALL PPA,
      PA_PROJECT_CUSTOMERS_V ppc
WHERE PE.EVENT_TYPE =PET.EVENT_TYPE
AND PET.EVENT_TYPE_ID IN (10120,10121,10124)
AND PE.BILLED_FLAG='N'
AND PE.PROJECT_ID IN
  (SELECT pe.project_id
    FROM  PA_EVENTS PE,
          PA_PROJECTS_ALL PA
WHERE 1=1
AND pe.revenue_distributed_flag='Y'
AND pa.project_id =pe.project_id)
AND ppa.project_id =pe.project_id
AND ppc.project_id =ppa.project_id
ORDER BY MONTH DESC;

Query to Get Employee Leave Details in Project Expense Moduke

SELECT PAPF.EMPLOYEE_NUMBER,
       PAPF.FULL_NAME "EMPLOYEE NAME" ,
        T.DESCRIPTION "LEAVE DESC",
       TO_DATE(trunc(ei.EXPENDITURE_ITEM_DATE),'DD-MON-YYYY') "DATE",
       EI.QUANTITY "LEAVE HOUR",
       PEC.EXPENDITURE_COMMENT
   FROM pa_projects_all p,
          pa_tasks t,
          pa_expenditure_items_all ei,
          pa_expenditures_all x,
          pa_project_types_all pt,
          pa_transaction_sources tr,
          hr_all_organization_units_tl o1,
          per_jobs j,
          per_all_people_f papf,
          pa_expenditure_comments pec
    WHERE t.project_id = p.project_id
      AND ei.project_id = p.project_id
      AND p.project_type = pt.project_type
      AND p.org_id = pt.org_id
      AND ei.task_id = t.task_id
      AND ei.expenditure_id = x.expenditure_id
      AND NVL (ei.override_to_organization_id, x.incurred_by_organization_id) =
                                                            o1.organization_id
      AND ei.job_id = j.job_id(+)
      AND ei.transaction_source = tr.transaction_source(+)
      AND P.PROJECT_ID =20006
     -- and ei.EXPENDITURE_TYPE='Leave'
     -- AND X.PERSON_TYPE='EMP'
      AND papf.person_id =  x.incurred_by_person_id
      AND  ei.expenditure_item_id =pec.expenditure_item_id(+)
      AND papf.FULL_NAME='SHIRKE, KUNAL S'
      order by ei.EXPENDITURE_ITEM_DATE asc;

Query AR Invoice Details with Tax in India Locallization

SELECT   jat.trx_number, jatl.line_number trx_line_number, jattl.tax_line_no,
         jatl.line_amount, ax.tax_type, ax.tax_name,
         SUM (jattl.tax_amount) tax_amount
    FROM jai_ar_trxs jat,
         jai_ar_trx_lines jatl,
         jai_ar_trx_tax_lines jattl,
         jai_cmn_taxes_all ax
   WHERE jat.customer_trx_id = jatl.customer_trx_id
     AND jattl.link_to_cust_trx_line_id = jatl.customer_trx_line_id
     AND ax.tax_id = jattl.tax_id
     AND jattl.tax_amount <> 0
     AND jat.complete_flag = 'Y'
     --AND trx_number = '100025'
     AND TRUNC (jat.creation_date) BETWEEN '01-AUG-2015' AND '30-SEP-2015'
GROUP BY jat.trx_number,
         jatl.line_number,
         jattl.tax_line_no,
         jatl.line_amount,
         ax.tax_type,
         ax.tax_name;

Query Project Agreement with Project Details

SELECT d.customer_id,
   ppc.customer_name,
       d.agreement_type,
       d.description agreement_desc,
       d.CUSTOMER_ORDER_NUMBER,
      AGREEMENT_NUM,TRUNC(D.CREATION_DATE) AGGREMENT__DATE,
    TRUNC(D.LAST_UPDATE_DATE) AGREMENT_LAST_UPDATE_DATE,
  SEGMENT1 PROJECT_NUMBER,
  c.project_type,
--  NAME PROJECT_NAME,
   LONG_NAME PROJECT_LONG_NAME,
       --TASK_NUMBER,
      ALLOCATED_AMOUNT,
      TRUNC(A.CREATION_DATE) funding_creation_date,
      TRUNC(A.LAST_UPDATE_DATE) funding_last_update_date,
      a.FUNDING_CURRENCY_CODE,
      a.PROJECT_CURRENCY_CODE,
      a.PROJFUNC_CURRENCY_CODE,
      a.PROJFUNC_ALLOCATED_AMOUNT,
       submit_baseline_flag
FROM pa_project_fundings A,
--pa_tasks b,
pa_projects_all c, pa_agreements_all d,
pa_project_customers_v ppc,
pa_project_contacts_v  ppct
WHERE a.project_id = c.project_id
AND a.agreement_id = d.agreement_id
and ppc.project_id = c.project_id
and c.project_id = ppct.project_id (+)
AND c.project_type='TMIND Contract';

Query AP Expense report Query of Employee

SELECT GSOB.NAME "SOB Name",
       AEH.INVOICE_NUM "Report Number",
       AEH.TOTAL,
       AEH.DESCRIPTION,
       PPX.FULL_NAME,
       PPX.EMPLOYEE_NUMBER,
       decode(AEH.WORKFLOW_APPROVED_FLAG,'S','SAVED','I','IMPLICIT SAVE','R','MANAGER REJECTED','M','MANAGER APPROVED','P','PAYABLES APPROVED','A','AUTO APPROVED','W','WITHDRAWN','Y','APPROVED','IN PROGRESS') Status,
       (SELECT APPS.AP_WEB_POLICY_UTILS.GET_LOOKUP_MEANING('EXPENSE REPORT STATUS',
                                                      DECODE(AI.CANCELLED_DATE,
                                                             NULL,
                                                             AEH.EXPENSE_STATUS_CODE,
                                                             'CANCELLED'))
          FROM APPS.AP_INVOICES_ALL AI
         WHERE AI.INVOICE_ID(+) = AEH.VOUCHNO) "Report status",
       NVL(AEH.REPORT_SUBMITTED_DATE, AEH.CREATION_DATE) "Report submitted on",
       CASE
         WHEN NVL2(AAP.ENTERED_DATE, 'Y', 'N') = 'Y' THEN
          AAP.ENTERED_DATE
       END "Manager Approved on",
       AEH.EXPENSE_STATUS_CODE,
       TRUNC(AEH.WEEK_END_DATE) "Report entered date",
       ACA.EMPLOYEE_NUMBER "Auditor Emp Num",
       ACA.FULL_NAME "Auditor Name",
       ACA.ENTERED_DATE "Audited on"
  FROM APPS.GL_SETS_OF_BOOKS GSOB,
       APPS.AP_EXPENSE_REPORT_HEADERS_ALL AEH,
       APPS.PER_PEOPLE_X PPX,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               AN.NOTE_ID,
               AN.SOURCE_OBJECT_ID,
               AN.ENTERED_BY,
               AN.ENTERED_DATE,
               PPX.EMPLOYEE_NUMBER,
               PPX.FULL_NAME
          FROM APPS.AP_NOTES AN, APPS.FND_USER FU, APPS.PER_PEOPLE_X PPX
         WHERE AN.ENTERED_BY = FU.USER_ID
           AND FU.EMPLOYEE_ID = PPX.PERSON_ID
           AND AN.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(AN.NOTES_DETAIL, 1, 24)) =
               'Approver Action: Approve') AAP,
       (SELECT ROW_NUMBER() OVER(PARTITION BY SOURCE_OBJECT_ID ORDER BY NOTE_ID DESC) SRL,
               ANS.NOTE_ID,
               ANS.SOURCE_OBJECT_ID,
               ANS.ENTERED_BY,
               ANS.ENTERED_DATE,
               PPLX.EMPLOYEE_NUMBER,
               PPLX.FULL_NAME
          FROM APPS.AP_NOTES ANS, APPS.FND_USER FUR, APPS.PER_PEOPLE_X PPLX
         WHERE ANS.ENTERED_BY = FUR.USER_ID
           AND FUR.EMPLOYEE_ID = PPLX.PERSON_ID
           AND ANS.SOURCE_OBJECT_CODE = 'OIE_EXPENSE_REPORT'
           AND TO_CHAR(SUBSTR(ANS.NOTES_DETAIL, 1, 15)) = 'Complete Audit.') ACA
 WHERE 1 = 1
   AND AEH.REPORT_HEADER_ID = ACA.SOURCE_OBJECT_ID(+)
   AND AEH.REPORT_HEADER_ID = AAP.SOURCE_OBJECT_ID(+)
   AND AEH.EMPLOYEE_ID = PPX.PERSON_ID
   AND GSOB.SET_OF_BOOKS_ID = AEH.SET_OF_BOOKS_ID
   AND AEH.EXPENSE_STATUS_CODE NOT IN ('INVOICED', 'PAID', 'WITHDRAWN', 'REJECTED')
   AND NVL (AAP.SRL, 1) = 1
   AND NVL (ACA.SRL, 1) = 1
 --  AND AEH.CREATION_DATE BETWEEN TO_DATE (:DATE_FROM_DDMONYYYY, 'DD-MON-YYYY') AND  TO_DATE (:DATE_TO_DDMONYYYY, 'DD-MON-YYYY')
   AND TRUNC (SYSDATE) BETWEEN TRUNC (PPX.EFFECTIVE_START_DATE) AND TRUNC (PPX.EFFECTIVE_END_DATE);
   AND AEH.REPORT_HEADER_ID=66989
   AND AEH.INVOICE_NUM ='EX66989';

Query Project with Revenue Amount with Event details

BEGIN
mo_global.set_policy_context('S',84);
END;

SELECT project_number, project_name, :p_gl_period MONTH, ou.NAME org_name,
       (SELECT revenue_amount
          FROM pa_events_v pae
         WHERE pae.event_id = pee.event_id
           AND UPPER (event_type) = UPPER ('Revenue')) rev_amt,
       NULL inter_crev_amt,
         NVL ((SELECT revenue_amount
                 FROM pa_events_v pae
                WHERE UPPER (event_type) = UPPER ('TMIND CoGS')
                  AND pae.event_id = pee.event_id),
              0
             )
       + NVL ((SELECT revenue_amount
                 FROM pa_events pae
                WHERE UPPER (event_type) = UPPER ('TMIND PC Labor CoGS')
                  AND pae.event_id = pee.event_id),
              0
             ) cogs_total,
       (SELECT revenue_amount
          FROM pa_events_v pae
         WHERE UPPER (event_type) =
                                  UPPER ('TMIND Warranty CoGS')
           AND pae.event_id = pee.event_id) cogs_warranty
  FROM pa_events_v pee, hr_all_organization_units_tl ou
 WHERE TO_CHAR (pee.event_date, 'MON-YYYY') = :p_gl_period
   AND ou.organization_id = pee.organization_id;

Query AR Invoice Aging Query

SELECT  pa.segment1 project_number,
       pa.name project_name,
       XX_GET_AGREEMENT_Num( pa.project_id) agreement_number,
      ppc.customer_name,
     (CASE WHEN ppc.customer_id  in (4046,12040,5040,5044) THEN 'Internal'
      ELSE 'External'        
      END) Internal_Or_External,
     (SELECT FLEX_VALUE_MEANING
     FROM FND_FLEX_VALUES_VL
     WHERE FLEX_VALUE_SET_ID=1015618
     AND DESCRIPTION IN (SELECT NAME FROM HR_ORGANIZATION_UNITS
     WHERE organization_id =pa.carrying_out_organization_id)
      ) cost_center,
      (SELECT NAME FROM hr_organization_units WHERE organization_id =pa.carrying_out_organization_id ) Business_unit,
       rcta.trx_number invoice_number,
       TRUNC(rcta.trx_date) invoice_date,
       rcta.term_due_date invoice_due_date,
       apsa.AMOUNT_DUE_REMAINING outstanding_amount,
        (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
               WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN  (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN  (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
              WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120  THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
        END) DAYS,
       apsa.AMOUNT_DUE_REMAINING current_1,
       (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END)past_Due_1_30,
      (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
        END) past_due_31_60,
         (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END) past_due_61_90,
      (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN  apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
         END) past_due_91_120,
       (CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN apsa.AMOUNT_DUE_REMAINING
         ELSE NULL
        END) past_due_121_Plus
  FROM RA_CUSTOMER_TRX_ALL RCTA,
       RA_CUSTOMER_TRX_LINES_ALL RCTLA,
       AR_PAYMENT_SCHEDULES_ALL apsa,
       PA_PROJECTS_ALL PA,
       PA_PROJECT_CUSTOMERS_V ppc
     --  PA_AGREEMENTS_ALL paa,
     --  PA_PROJECT_FUNDINGS ppf  
WHERE 1=1
--AND RCTA.TRX_NUMBER='100000'
AND RCTA.STATUS_TRX='OP'
AND rcta.customer_trx_id =rctla.customer_trx_id
AND apsa.customer_trx_id =rcta.customer_trx_id
AND apsa.customer_trx_id  =rctla.customer_trx_id
AND pa.segment1= rcta.interface_header_attribute1
AND ppc.project_id =pa.project_id
AND rcta.sold_to_customer_id =ppc.customer_id
--AND paa.agreement_id =ppf.agreement_id
--AND ppc.customer_id = paa.customer_id
--AND substr(paa.description,2,5)=pa.segment1
 ORDER BY  pa.segment1 ASC;

Script to Assign and Unassign the delivery no

--Script to Assign and Unassign the delivery no
begin
mo_global.set_policy_context('S',183);
end;

183 -HS1, 184-BS1


DECLARE

    P_API_VERSION        NUMBER;
    REQUESTID            NUMBER;
    l_layout             BOOLEAN;
    l_del_no             VARCHAR2(100);
    v_counter            NUMBER;
    P_INIT_MSG_LIST     VARCHAR2(200);
    P_COMMIT            VARCHAR2(200);
    P_VALIDATION_LEVEL  NUMBER;
    X_RETURN_STATUS      VARCHAR2(200);
    X_MSG_COUNT          NUMBER;
    P_TABOFDELDETS      APPS.WSH_DELIVERY_DETAILS_PUB.ID_TAB_TYPE;
    P_ACTION             VARCHAR2(200);
    P_DELIVERY_ID        NUMBER;
    P_DELIVERY_NAME      VARCHAR2(200);
    P_COUNT              NUMBER;
    V_CONTEXT            VARCHAR2(100);
    v_counter_1          NUMBER;
    l_organization_id     NUMBER;
    l_organization_code   VARCHAR2 (10);
    l_return_statuss      VARCHAR2 (2);
    X_MSG_DATA            VARCHAR2 (4000);
    L_REQUEST_ID          FND_CONCURRENT_REQUESTS.REQUEST_ID%TYPE;
    L_STATUS_CODE         FND_CONCURRENT_REQUESTS.STATUS_CODE%TYPE := '~';
    x_del_rows            wsh_util_core.id_tab_type;
    x_line_rows           wsh_util_core.id_tab_type;
    l_debug_level         NUMBER := 0;                -- OM DEBUG LEVEL (MAX 5)
    l_return_status       VARCHAR2 (100);
    l_msg_count           NUMBER;
    l_msg_data            VARCHAR2 (1000);
    l_del_rows            NUMBER;
    l_user_id             NUMBER;
    l_batch_id            NUMBER;
    l_batch_rec           WSH_PICKING_BATCHES_PUB.batch_info_rec;


CURSOR cur_main IS
 SELECT   wdd.delivery_detail_id, wdd.organization_id,wnd.delivery_id,wnd.name
      ,wdd.source_header_type_name
          FROM   wsh_delivery_details wdd,wsh_delivery_assignments wda,
      wsh_new_deliveries wnd
         WHERE       wdd.organization_id = p_org_id AND wdd.delivery_detail_id =
      wda.delivery_detail_id
      AND wda.delivery_id =wnd.delivery_id
                 AND wdd.released_status = 'S'
                 AND wdd.customer_id = p_cust_id --5346
                       ORDER BY   wdd.customer_id;
                     

CURSOR cur_assign IS  SELECT   delivery_detail_id, organization_id
          FROM   wsh_delivery_details ws
         WHERE       organization_id =p_org_id
                 AND released_status = 'S'
                 AND customer_id = p_cust_id
                 AND NOT EXISTS
                       (SELECT   delivery_detail_id
                          FROM   wsh_delivery_assignments
                         WHERE   delivery_detail_id = ws.delivery_detail_id
                                 AND delivery_id IS NOT NULL)
      ORDER BY   customer_id;


 BEGIN
     DBMS_OUTPUT.PUT_LINE('1');

        MO_GLOBAL.init('ONT');
 
 
  P_API_VERSION      := 1.0;
  P_INIT_MSG_LIST    := FND_API.G_TRUE;
  P_COMMIT           := FND_API.G_TRUE;
  P_VALIDATION_LEVEL := FND_API.G_VALID_LEVEL_FULL;

  -- Initialize the PLSQL table type variable with 'N' number of Deliverie Detail id's
 --P_TABOFDELDETS(1)  := 14342581;
  v_counter :=0;

  --P_TABOFDELDETS(1)  := 216028;
         FOR  i IN cur_main
            LOOP

            v_counter := v_counter + 1;
            P_TABOFDELDETS(v_counter) :=i.delivery_detail_id;
            P_ACTION           := 'UNASSIGN';
            P_DELIVERY_ID      := i.delivery_id;
            P_DELIVERY_NAME    :=  i.name;
          END LOOP;

        P_ACTION           := 'ASSIGN';  -- Provide the proper Action Type
        P_DELIVERY_ID      := 20010;    --null;      -- Delivery id from which the delivery detail to be detached
        P_DELIVERY_NAME    := '20010';    -- respective delivery name

   WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY (
              P_API_VERSION      => P_API_VERSION,
              P_INIT_MSG_LIST    => P_INIT_MSG_LIST,
              P_COMMIT           => P_COMMIT,
              P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
              X_RETURN_STATUS    => X_RETURN_STATUS,
              X_MSG_COUNT        => X_MSG_COUNT,
              X_MSG_DATA         => X_MSG_DATA,
              P_TABOFDELDETS     => P_TABOFDELDETS,
              P_ACTION           => P_ACTION,
              P_DELIVERY_ID      => P_DELIVERY_ID,
              P_DELIVERY_NAME    => P_DELIVERY_NAME
            );

      IF X_RETURN_STATUS = 'S' THEN
       DBMS_OUTPUT.PUT_LINE('Unassigned Sucessfully ');
      ELSE
       DBMS_OUTPUT.PUT_LINE('Message count ' || X_MSG_COUNT);

                   IF X_MSG_COUNT = 1 THEN
                      DBMS_OUTPUT.PUT_LINE('X_msg_data '||X_MSG_DATA);
                   ELSIF X_MSG_COUNT > 1 THEN
                   LOOP
                      P_COUNT := P_COUNT+1;
                      X_MSG_DATA := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
                      IF X_MSG_DATA IS NULL THEN
                      EXIT;
                      END IF;
                      DBMS_OUTPUT.PUT_LINE('Message' || P_COUNT ||'---'||X_MSG_DATA||' '||
                      SQLERRM||SQLCODE);

                      END LOOP;
                   END IF;

      END IF;


             BEGIN
                  v_counter_1 := 0;

                  FOR r_delivery IN cur_assign
                  LOOP
                     v_counter_1 := v_counter_1 + 1;
                     l_organization_id := r_delivery.organization_id;
                     x_line_rows (v_counter_1) := r_delivery.delivery_detail_id;
                  END LOOP;

                  IF x_line_rows.COUNT > 0
                  THEN
                     wsh_delivery_details_pub.autocreate_deliveries (
                        p_api_version_number   => 1.0,
                        p_init_msg_list        => fnd_api.g_true,
                        p_commit               => fnd_api.g_true,
                        x_return_status        => x_return_status,
                        x_msg_count            => x_msg_count,
                        x_msg_data             => x_msg_data,
                        p_line_rows            => x_line_rows,
                        x_del_rows             => x_del_rows
                     );

                     IF (x_return_status <> wsh_util_core.g_ret_sts_success)
                     THEN
                        --fnd_file.PUT_LINE (fnd_file.OUTPUT, 'Failure');
                        DBMS_OUTPUT.put_line ('Failure  ' || SQLERRM || SQLCODE);
                        NULL;
                     ELSE
                        COMMIT;
                        --               fnd_file.PUT_LINE (fnd_file.OUTPUT, 'Success');

                        DBMS_OUTPUT.put_line ('Sucess  ');
                        DBMS_OUTPUT.put_line (x_del_rows (1));
                        l_del_rows := x_del_rows (1);
                        l_del_no := x_del_rows (1);
                        COMMIT;
         
   
                     END IF;
                  END IF;
               END;

    END;

Oracle R12 - RFQ to PO Receipt Cycle

Oracle R12 - RFQ to PO Receipt Cycle

This Post is about Oracle applications RFQ to Receipt Cycle.In this Post I will explain the Cycle from RFQ to PO Receipt with screen shots.

High -Lights of this Cycle is  to Create:

• RFQ
• Quote
• Purchase Order
• Receive against PO.

Once RFQ is created , Submit concurrent job “Request to Print the RFQ” for a supplier. On completion this job will increment print count for that supplier as shown below.

In below  Oracle Apps UIs we can see

1. RFQ # 308,
2. Supplier Info from Supplier List and
3. Price Breaks.

Print RFQ for all the Suppliers by means of Concurrent Program available in Oracle Apps

Once we print the RFQ , status of RFQ become Printed , and print count will increment for all suppliers .Since we got response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for this supplier only . 

  

From the RFQ , Select Tools &amp;gt; Copy Doc .It will Create Quotations as shown below.

1. Enter the Supplier Name for whom you want to create Quote.
2. Press OK and it will Create Quotation.

1.  Query for Quotation # 502.
2. Create Purchase Order Agreement from Quotation by selected Tools &amp;gt; Copy Doc
3. Press Ok and it will Create Purchase Order Agreement. 

 Query for PO Agreement and Approve it

Once Oracle Purchase agreement got approved , creates the releases for Blanket PO Agreement .In our example PO Agreement Release we have item Test001 , BUT Item Test001 has restricted to be ordered from supplier from “Approval Supplier list”, and as  our Supplier is not part of any Approve Supplier list , system will throw Error.

 For my test , I just remove the Item Test001 and Approve the Oracle Purchase Order Release and finally did the receipt against PO.

Interface Trip Stop Functionality

Interface Trip Stop Functionality
There are typically four interface program get triggered at the time of ship confirm.
They are
Interface Trip stop
Packing Slip Report
Bill of Lading
Invoice
Interface Trip Stop is one of the main interface programs which can be triggered at
the time of ship confirm or later as Interface Trip stop SRS
concurrent request.
Interface trip stop has two parts
Order Management Interface (First part)
Inventory Interface (Second part)
Order Management Interface part update the order management tables like
OE_ORDER_LINES_All (Update Shipped_Quantity, Shipping_Quantity,
Actual_Shipment_Date) and also WSH_DELIVERY_DETAILS (make
Released_status, OE_INTERFACED_FLAG = ‘Y’) table.
The Inv_Interfaced_Flag remains = X (Pending) or N which will changes to Y after
execution of second part
The second part i.e. Inventory Interface will be triggered only if the value of
OE_INTERFACED_FLAG = ‘Y’ otherwise second part will not be triggered. Even if
you try to run this from SRS, these lines will not be picked for processing.
Inventory Interface part move data from MTL_TRANSACTIONS_INTERFACE to
MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is
updated with Sales Order Issue transaction. Also data is deleted from
MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES. Item
quantity reduced from MTL_ONHAND_QUANTITIES and
MTL_TRANSACTION_ACCOUNTS is updated with accounting information.
Both these parts are executed simultaneously

API to Update the List price of PO

API to Update the List price of PO

Declare  
   v_result           NUMBER;
   v_api_errors       po_api_errors_rec_type;
   v_revision_num     po_headers_all.revision_num%TYPE :=0;
   v_price            po_lines_all.unit_price%TYPE:=200;
   v_quantity         po_line_locations_all.quantity%TYPE :=10;
   v_po_number        po_headers_all.segment1%TYPE :='145';
   v_line_num         po_lines_all.line_num%TYPE :=1;
   v_shipment_num     po_line_locations_all.shipment_num%TYPE:=1;
   v_promised_date    DATE :='20-JUL-11';
   v_need_by_date     DATE :=' ';--:='11-JUL-2011';
   v_org_id           NUMBER :=85;
   v_context          VARCHAR2 (10);
   v_user_id          NUMBER :=1118;
   v_resp_id          NUMBER;
BEGIN
  
   SELECT level_value
     INTO v_resp_id
     FROM fnd_profile_option_values
    WHERE profile_option_id = 1991
      AND level_value_application_id = 201
      AND profile_option_value = v_org_id;
   fnd_global.apps_initialize (v_user_id, v_resp_id,201);
   mo_global.init ('PO');
  
   v_result :=
      po_change_api1_s.update_po (x_po_number                => v_po_number,
                                  x_release_number           => NULL,
                                  x_revision_number          => v_revision_num,
                                  x_line_number              => v_line_num,
                                  x_shipment_number          => v_shipment_num,
                                  new_quantity               => v_quantity,
                                  new_price                  => v_price,
                                  new_promised_date          => v_promised_date,
                                  new_need_by_date           => v_need_by_date,
                                  launch_approvals_flag      => 'N',
                                  update_source              => NULL,
                                  VERSION                    => '1.0',
                                  x_override_date            => NULL,
                                  x_api_errors               => v_api_errors,
                                  p_buyer_name               => NULL,
                                  p_secondary_quantity       => NULL,
                                  p_preferred_grade          => NULL,
                                  p_org_id                   => v_org_id
                                 );
   DBMS_OUTPUT.put_line ('RESULT :' || v_result);
   Commit;
   IF (v_result = 1)
   THEN
      DBMS_OUTPUT.put_line ('Updating PO is Successful ');
   ELSE
      DBMS_OUTPUT.put_line ('Updating PO failed');
      FOR j in 1 .. v_api_errors.MESSAGE_TEXT.COUNT
      LOOP
         DBMS_OUTPUT.put_line (v_api_errors.MESSAGE_TEXT (j));
      END LOOP;
   END IF;
  
END;

AP-PO query

- VENDOR, PO AND INVOICE DETAILS (2 WAY MATCH)

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

-- VENDOR, PO AND INVOICE DETAILS (3 WAY MATCH)SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO AND INVOICE DETAILS (4 WAY MATCH)SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'

-- VENDOR, PO, INVOICE AND PAYMENT DETAILSSELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME

R12 TCA Bank Model

n Release 12, each internal bank account is assigned to a Legal Entity. Any or all operating units associated with that legal entity are permitted to use that bank account

• The link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id
• PO_VENDOR_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id
• When a Supplier is created, Record will be Inserted in HZ_PARTIES. When the Supplier Site is created, Record will be Inserted in HZ_PARTY_SITES. When Address is created, Record will be stored in HZ_LOCATIONS
• When a bank is Created, information will be stored in IBY_EXT_BANK_ACCOUNTS
  IBY_EXT_BANK_ACCOUNTS.bank_id = HZ_PARTIES.party_id
• When the Bank is assigned to Vendors then it will be updated in HZ_CODE_ASSIGNMENTS. HZ_CODE_ASSIGNMENTS.owner_table_id = IBY_EXT_BANK_ACCOUNTS.branch_id

       

1. CE_BANK_ACCOUNT: Stores bank account attributes
2. CE_BANK_ACCT_USES_ALL: Stores the bank account use attributes specific to Operating Unit (AR, AP) and Legal Entity (Treasury).
3. CE_GL_ACCOUNTS_CCID: The accounting data pertaining to the bank account use will be stored in the table

OE_ORDER_PUB.process_order API to Create Sales Order in Order Management

set serveroutput on

DECLARE

 l_header_rec OE_ORDER_PUB.Header_Rec_Type;

 l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;

 l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type;

 l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 l_request_rec OE_ORDER_PUB.Request_Rec_Type ;

 l_return_status VARCHAR2(1000);

 l_msg_count NUMBER;

 l_msg_data VARCHAR2(1000);

 p_api_version_number NUMBER :=1.0;

 p_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;

 p_return_values VARCHAR2(10) := FND_API.G_FALSE;

 p_action_commit VARCHAR2(10) := FND_API.G_FALSE;

 x_return_status VARCHAR2(1);

 x_msg_count NUMBER;

 x_msg_data VARCHAR2(100);

 p_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_old_header_rec OE_ORDER_PUB.Header_Rec_Type :=  OE_ORDER_PUB.G_MISS_HEADER_REC;

 p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC;

 p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;

 p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL;

 p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL;

 p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL;

 p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type :=  OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL;

 p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL;

 p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL;

 p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;

 p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL;

 p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;

 p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL;

 p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL;

 p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL;

 p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL;

 p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL;

 p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL;

 p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL;

 p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL;

 p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL;

 x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;

 x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;

 x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;

 x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;

 x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;

 x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;

 x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;

 x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;

 x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;

 x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;

 x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;

 x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;

 x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;

 x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;

 x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;

 x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;

 x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;

 x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

 x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;

 X_DEBUG_FILE VARCHAR2(100);

 l_line_tbl_index NUMBER;

 l_msg_index_out NUMBER(10);

BEGIN

 dbms_output.enable(1000000);

 fnd_global.apps_initialize(1318,21623,660); -- pass in user_id, responsibility_id, and application_id

 oe_msg_pub.initialize;

 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data

 dbms_output.put_line('START OF NEW DEBUG');

--This is to UPDATE order line

 l_line_tbl_index :=1;

-- Changed attributes

 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

 l_header_rec.header_id := 98393; -- header_id of the order

 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

 l_line_tbl(l_line_tbl_index).split_by := 4096; -- user_id

 l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index).header_id := 98393; -- header_id of the order

 l_line_tbl(l_line_tbl_index).line_id := 200995; -- line_id of the order line

 l_line_tbl(l_line_tbl_index).ordered_quantity := 3; -- new ordered quantity

 l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- change reason code

 l_line_tbl_index :=2;

 l_line_tbl(l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index ).operation := OE_GLOBALS.G_OPR_CREATE;

 l_line_tbl(l_line_tbl_index ).split_by := 4096; -- user_id

 l_line_tbl(l_line_tbl_index ).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index ).split_from_line_id := 200995; -- line_id of  original line

 l_line_tbl(l_line_tbl_index ).inventory_item_id := 149; -- inventory item id

 l_line_tbl(l_line_tbl_index ).ordered_quantity := 1; -- ordered quantity

-- CALL TO PROCESS ORDER

 OE_ORDER_PUB.process_order (

  p_api_version_number => 1.0

  , p_init_msg_list => fnd_api.g_false

  , p_return_values => fnd_api.g_false

  , p_action_commit => fnd_api.g_false

  , x_return_status => l_return_status

  , x_msg_count => l_msg_count

  , x_msg_data => l_msg_data

  , p_header_rec => l_header_rec

  , p_line_tbl => l_line_tbl

  , p_action_request_tbl => l_action_request_tbl

-- OUT PARAMETERS

  , x_header_rec => l_header_rec

  , x_header_val_rec => x_header_val_rec

  , x_Header_Adj_tbl => x_Header_Adj_tbl

  , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl

  , x_Header_price_Att_tbl => x_Header_price_Att_tbl

  , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl

  , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl

  , x_Header_Scredit_tbl => x_Header_Scredit_tbl

  , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl

  , x_line_tbl => l_line_tbl

  , x_line_val_tbl => x_line_val_tbl

  , x_Line_Adj_tbl => x_Line_Adj_tbl

  , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl

  , x_Line_price_Att_tbl => x_Line_price_Att_tbl

  , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl

  , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl

  , x_Line_Scredit_tbl => x_Line_Scredit_tbl

  , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl

  , x_Lot_Serial_tbl => x_Lot_Serial_tbl

  , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl

  , x_action_request_tbl => l_action_request_tbl

 );

 dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages

 FOR i IN 1 .. l_msg_count

 LOOP

  Oe_Msg_Pub.get( p_msg_index => i

  , p_encoded => Fnd_Api.G_FALSE

  , p_data => l_msg_data

  , p_msg_index_out => l_msg_index_out);

  DBMS_OUTPUT.PUT_LINE('message is: ' || l_msg_data);

  DBMS_OUTPUT.PUT_LINE('message index is: ' || l_msg_index_out);

 END LOOP;

-- Check the return status

 IF l_return_status = FND_API.G_RET_STS_SUCCESS

 THEN

  dbms_output.put_line('Line Quantity Update Sucessful');

 ELSE

  dbms_output.put_line('Line Quantity update Failed');

 END IF;

END;