Oracle Purchase Order using Interface po_headers_interface

Create Purchase Orders using standard Oracle Interface:


 
CREATE OR REPLACE PROCEDURE xxx_parts_order_create_po (p_vendor_id IN NUMBER,p_vendor_site_id IN NUMBER,p_currency_code IN VARCHAR2,p_po_type IN VARCHAR2,p_inco_terms IN VARCHAR2)
AUTHID
IS CURRENT_USERx_po_header_id NUMBER;x_po_line_id NUMBER;l_organization_id NUMBER;ln_amount NUMBER;ln_list_price NUMBER;ln_unit_cost NUMBER;ln_cust_price NUMBER;ln_op_unit_cost NUMBER;
CURSOR c2IS
SELECT inventory_item_id, item_name, item_desc description-- , 1000 amount --Put calculated value here,uom primary_uom_code

, dealer_po_line_id, order_quantityFROM gaa_dealer_po_creationWHERE NVL (create_po, 'X') = 'Y' AND NVL (po_created, 'X') <> 'Y';wc2 c2%ROWTYPE;l_user_emp_id NUMBER;l_item_account_id NUMBER;l_location_id NUMBER;l_user_id NUMBER;l_incident_number NUMBER;l_org_id NUMBER;l_sob_id NUMBER;l_sob_currency VARCHAR2 (15);l_line_no NUMBER;l_request_id
BEGINNUMBER;l_user_id := fnd_profile.VALUE ('USER_ID');l_org_id := fnd_profile.VALUE ('ORG_ID');l_organization_id := fnd_profile.VALUE ('GAA_ORGANIZATION_ID');l_sob_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');BEGIN


SELECT usr.employee_idINTO l_user_emp_idFROM fnd_user usrWHERE usr.user_id = fnd_global.user_id;EXCEPTIONWHEN OTHERSTHENNULL;END;BEGINSELECT MAX (loc.location_id)

INTO l_location_idFROM hr_locations locWHERE loc.inventory_organization_id = l_organization_id;EXCEPTIONWHEN OTHERSTHENNULL;END;BEGIN


SELECT iac.material_accountINTO l_item_account_idFROM mtl_parameters iacWHERE iac.organization_id = l_organization_id;EXCEPTIONWHEN OTHERSTHENNULL;END;SELECT po_headers_interface_s.NEXTVAL
INTO x_po_header_idFROM DUAL;
INSERT INTO po_headers_interface(interface_header_id, document_type_code, vendor_id,vendor_site_id, currency_code, approval_required_flag,org_id, agent_id, action, interface_source_code, batch_id,attribute1
, attribute2)VALUES (x_po_header_id, 'STANDARD', p_vendor_id,p_vendor_site_id, p_currency_code, 'N',l_org_id, l_user_emp_id, 'ORIGINAL', 'VEH', 1,p_po_type
, p_inco_terms);OPEN c2;LOOP
FETCH c2INTO wc2;EXIT WHEN c2%NOTFOUND;BEGIN



SELECT PLV.unit_price, PLV.attribute3INTO ln_amount, ln_list_priceFROM po_lines_v PLVWHERE PLV.item_number = wc2.item_nameAND po_line_id =(SELECT MAX (pl.po_line_id)


FROM po_lines_v pl, po_headers_all phWHERE pl.item_number = PLV.item_numberAND ph.po_header_id = pl.po_header_idAND ph.type_lookup_code = 'QUOTATION'

AND ph.vendor_id = p_vendor_idAND ph.vendor_site_id = p_vendor_site_idAND ph.currency_code = p_currency_code);EXCEPTIONWHEN OTHERSTHENNULL;END;l_line_no := NVL (l_line_no, 0) + 1;SELECT po_lines_interface_s.NEXTVAL
INTO x_po_line_idFROM DUAL;
INSERT INTO po_lines_interface(interface_line_id, interface_header_id, line_type_id,item_id, item_description, unit_price,uom_code, quantity, note_to_vendor,shipment_num, promised_date, line_num, line_attribute14,line_attribute3
)VALUES (x_po_line_id, x_po_header_id, 1,wc2.inventory_item_id, wc2.description, ln_amount,wc2.primary_uom_code, wc2.order_quantity, wc2.description,l_line_no, SYSDATE, l_line_no, wc2.dealer_po_line_id,ln_list_price
);
INSERT INTO po_distributions_interface(interface_line_id, interface_header_id,interface_distribution_id, distribution_num,quantity_ordered
, charge_account_id, org_id)VALUES (x_po_line_id, x_po_header_id,po_distributions_interface_s.NEXTVAL, l_line_no,1
, l_item_account_id, l_org_id);
UPDATE gaa_dealer_po_creationSET po_created = 'Y'WHERE dealer_po_line_id = wc2.dealer_po_line_id;END LOOP;CLOSE c2;COMMIT;l_request_id :=fnd_request.submit_request ('PO','POXPOPDOI',NULL,NULL,FALSE,'','STANDARD','','N','','APPROVED','',1,'','',CHR (0));
ENDCOMMIT; xxx_parts_order_create_po;This was done on Oracle Apps 11.5.10.2

Form Compilation Commands in Oracle Apps

Login to Application Server on Unix Box for Compiling Forms

R11i

f60gen module=CUSTOM.pll userid=apps/(appspwd) module_type=LIBRARY batch=NO compile_all=special output_file=$AU_TOP/resource/CUSTOM.plx

f60gen module=XXPOCF.fmb userid=apps/(appspwd) module_type=form batch=no compile_all=special output_file=$XXPO_TOP/forms/US/XXPOCF.fmx

R12

$ORACLE_HOME/bin/frmcmp_batch module_type=LIBRARY module=$AU_TOP/resource/CUSTOM.pll userid=apps/(appspwd) output_file=$AU_TOP/resource/CUSTOM.plx compile_all=special

$ORACLE_HOME/bin/frmcmp_batch module=$XXFND_TOP/forms/US/XXFND_FHLOG.fmb userid=apps/(appspwd) output_file=$XXFND_TOP/forms/US/XXFND_FHLOG.fmx module_type=form compile_all=special

Loading Data from Excel to Oracle Tables using TOAD

Follow the below steps to load data from Excel sheet to Oracle tables. This can be used wile data migration.

Step 1: Put your data in Excel Sheet. Remove column headers.

Step 2: Create Table in Oracle. Take care of Column data types

CREATE TABLE XX_TEST

(EMPLOYEE_NO NUMBER,

 ENAME       VARCHAR2 (50),

 SALARY       NUMBER

);

Step 3:

Step 4: Select the XX_TEST table and press on Show Data button.

Step 5 : Commit

How to Find Column Name in Oracle dba_tab_cols

Use Following Query to find the column name in Oracle

SELECT * FROM dba_tab_cols WHERE table_name LIKE 'FA%' AND column_name LIKE 'PO%'

Create Event in Oracle Projects - pa_event_pub.create_event

API to Create Event in Oracle Projects:

create or replace procedure xxpa_create_project_event(errbuf in varchar2,retcode in number)

as

l_msg_count    NUMBER;

l_msg_data     varchar2(2000);

l_return_status VARCHAR2(10);

l_event_in_tbl   PA_EVENT_PUB.Event_In_Tbl_Type;

l_event_out_tbl  PA_EVENT_PUB.Event_out_Tbl_Type;

ln_line_no number;

cursor cur is

select * from XXPA_CREATE_PRO_EVENT ;

begin

for i in cur

loop

ln_line_no := NVL(ln_line_no,0) + 1;

l_event_in_tbl (ln_line_no).p_pm_event_reference := i.project_number ;

l_event_in_tbl (ln_line_no).p_task_number := NULL;

l_event_in_tbl (ln_line_no).p_event_type :=

'Data Migration';

l_event_in_tbl (ln_line_no).p_description :=

'Data Migration';

l_event_in_tbl (ln_line_no).p_completion_date :=

to_date('31-DEC-2008');

l_event_in_tbl (ln_line_no).p_project_number :=

i.project_number;

l_event_in_tbl (ln_line_no).p_organization_name :=

'Eros Electricals';

l_event_in_tbl (ln_line_no).p_bill_trans_bill_amount := I.bill_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_rev_amount := I.revenue_amount;

l_event_in_tbl (ln_line_no).p_bill_trans_currency_code :=

'AED';

end loop;

pa_event_pub.create_event (p_api_version_number => 1.0,

p_commit => fnd_api.g_false,

p_init_msg_list => 'T',

p_pm_product_code => 'XXXX',

p_event_in_tbl => l_event_in_tbl,

p_event_out_tbl => l_event_out_tbl,

p_msg_count => l_msg_count,

p_msg_data => l_msg_data,

p_return_status => l_return_status

);

Fnd_File.put_line(Fnd_File.LOG, 'Error Message: '||l_msg_data);

dbms_output.put_line(l_msg_data);

commit;

end;

/

Cancel Order Line – Order Management oe_order_pub.process_order

API to cancel Order Line:

CREATE OR REPLACE PROCEDURE xxom_cancel_line (

   p_errbuf    IN   VARCHAR2,

   p_retcode   IN   NUMBER,

   p_validate in varchar2

)

AS

/*************************************/

/* Variables */

/*************************************/

   l_step                      VARCHAR2 (250);

   l_err_msg                   VARCHAR2 (1000);

   v                           VARCHAR2 (1000);

   l_line_tbl                  oe_order_pub.line_tbl_type;

   lx_return_status            VARCHAR2 (10000);

   lx_msg_count                NUMBER;

   lx_msg_data                 VARCHAR2 (10000);

   lx_header_rec               oe_order_pub.header_rec_type;

   lx_header_val_rec           oe_order_pub.header_val_rec_type;

   lx_header_adj_tbl           oe_order_pub.header_adj_tbl_type;

   lx_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;

   lx_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;

   lx_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;

   lx_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;

   lx_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;

   lx_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;

   lx_header_payment_tbl       oe_order_pub.header_payment_tbl_type;

   lx_header_payment_val_tbl   oe_order_pub.header_payment_val_tbl_type;

   lx_line_tbl                 oe_order_pub.line_tbl_type;

   lx_line_val_tbl             oe_order_pub.line_val_tbl_type;

   lx_line_adj_tbl             oe_order_pub.line_adj_tbl_type;

   lx_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;

   lx_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;

   lx_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;

   lx_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;

   lx_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;

   lx_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;

   lx_line_payment_tbl         oe_order_pub.line_payment_tbl_type;

   lx_line_payment_val_tbl     oe_order_pub.line_payment_val_tbl_type;

   lx_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;

   lx_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;

   lx_action_request_tbl       oe_order_pub.request_tbl_type;

   CURSOR cur_so

   IS

      SELECT ool.header_id, ool.line_id

        FROM oe_order_lines_all ool, oe_order_headers_all ooh

       WHERE ool.flow_status_code = 'AWAITING_SHIPPING'

         AND ool.header_id = ooh.header_id

         AND ooh.order_type_id = 1202

         AND schedule_ship_date < TO_DATE ('24-Feb-2009');

/*************************************/

/* Main */

/*************************************/

BEGIN

   DELETE FROM test_adel;

   mo_global.set_policy_context ('S', 82);

   fnd_global.apps_initialize (1090, 21623, 660);

   fnd_client_info.set_org_context (82);

   fnd_file.put_line (fnd_file.LOG, 'P_header_id = ' || 40574);

   fnd_file.put_line (fnd_file.LOG, 'P_line_id = ' || 59116);

   l_step := 'Insert values into l_line_tbl';

   FOR i IN cur_so

   LOOP

      l_line_tbl (1) := oe_order_pub.g_miss_line_rec;

      l_line_tbl (1).ordered_quantity := 0;

      l_line_tbl (1).header_id := i.header_id;

      l_line_tbl (1).line_id := i.line_id;

      l_line_tbl (1).change_reason := 1;

      -- 'Test'; --'No reason provided';

      l_line_tbl (1).operation := oe_globals.g_opr_update;

      l_step := 'Run API OE_ORDER_PUB.PROCESS_ORDER';

      apps.oe_order_pub.process_order

                      (p_api_version_number          => 1.0,

                       p_init_msg_list               => fnd_api.g_true,

                       p_return_values               => fnd_api.g_true,

                       p_action_commit               => fnd_api.g_true,

                       x_return_status               => lx_return_status,

                       x_msg_count                   => lx_msg_count,

                       x_msg_data                    => lx_msg_data,

                       p_line_tbl                    => l_line_tbl,

                       x_header_rec                  => lx_header_rec,

                       x_header_val_rec              => lx_header_val_rec,

                       x_header_adj_tbl              => lx_header_adj_tbl,

                       x_header_adj_val_tbl          => lx_header_adj_val_tbl,

                       x_header_price_att_tbl        => lx_header_price_att_tbl,

                       x_header_adj_att_tbl          => lx_header_adj_att_tbl,

                       x_header_adj_assoc_tbl        => lx_header_adj_assoc_tbl,

                       x_header_scredit_tbl          => lx_header_scredit_tbl,

                       x_header_scredit_val_tbl      => lx_header_scredit_val_tbl,

                       x_header_payment_tbl          => lx_header_payment_tbl,

                       x_header_payment_val_tbl      => lx_header_payment_val_tbl,

                       x_line_tbl                    => lx_line_tbl,

                       x_line_val_tbl                => lx_line_val_tbl,

                       x_line_adj_tbl                => lx_line_adj_tbl,

                       x_line_adj_val_tbl            => lx_line_adj_val_tbl,

                       x_line_price_att_tbl          => lx_line_price_att_tbl,

                       x_line_adj_att_tbl            => lx_line_adj_att_tbl,

                       x_line_adj_assoc_tbl          => lx_line_adj_assoc_tbl,

                       x_line_scredit_tbl            => lx_line_scredit_tbl,

                       x_line_scredit_val_tbl        => lx_line_scredit_val_tbl,

                       x_line_payment_tbl            => lx_line_payment_tbl,

                       x_line_payment_val_tbl        => lx_line_payment_val_tbl,

                       x_lot_serial_tbl              => lx_lot_serial_tbl,

                       x_lot_serial_val_tbl          => lx_lot_serial_val_tbl,

                       x_action_request_tbl          => lx_action_request_tbl

                      );

     

      fnd_file.put_line (fnd_file.LOG, 'Return Status -' || lx_return_status);

      IF lx_return_status = 'S'

      THEN

         fnd_file.put_line (fnd_file.LOG, 'Success - Line cancelled');

      END IF;

      IF lx_msg_count > 0

      THEN

         DBMS_OUTPUT.put_line

                        ('Listing Errors .................for Order Number -');

         FOR l_index IN 1 .. lx_msg_count

         LOOP

            DBMS_OUTPUT.put_line

                        ('Listing Errors .................for Order Number -');

            v :=

               apps.oe_msg_pub.get (p_msg_index      => l_index,

                                    p_encoded        => apps.fnd_api.g_false

                                   );

            INSERT INTO test_adel

                        (error

                        )

                 VALUES (v

                        );

         END LOOP;

      END IF;

   END LOOP;

   if p_validate = 'Commit' then

     commit;

   else

   rollback;

   end if;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := 'Error in script ETALCANCELCI.sql at: ' || l_step || ': ';

      l_err_msg := l_err_msg || SQLERRM;

      fnd_file.put_line (fnd_file.LOG, CHR (10) || l_err_msg);

      RAISE;

END xxom_cancel_line;

Apply Hold in Order Management (OM) Oe_Holds_Pub.Apply_Holds

API to Apply Holds in Oracle Order Management:

CREATE OR REPLACE PROCEDURE APPS.Xx_Om_Apply_SO_HOLD(p_trip_number in varchar2,p_err_msg out varchar2) IS

lv_order_rec   Oe_Holds_Pvt.order_rec_type;

lv_order_tbl   Oe_Holds_Pvt.order_tbl_type;

lv_return_status VARCHAR2(10);

ln_msg_count     NUMBER;

lv_msg_data      VARCHAR2(200);

ln_line_no       number  := 0;

lv_comment       varchar2(100);

 l_msg_index_out number;

ln_hold_id       number ;

lv_hold_name     varchar2(50) := fnd_profile.value('XXXX_VEHICLE_VOLUME_HOLD');

cursor cur_hold

is

 SELECT DISTINCT OOH.header_id

 FROM XX_OM_MTRIP_LINES_V XXL,

      oe_order_headers_v  OOH

 WHERE XXL.trip_number = p_trip_number

 AND XXL.put_on_hold = 'Y'

 AND XXL.SALES_ORDER = OOH.Order_number         

 MINUS

 SELECT Header_id

 FROM OE_HOLDS_HISTORY_V

 WHERE Hold_name  = lv_hold_name

 AND Released_flag = 'N'

 order by 1; 

BEGIN

  FOR I in cur_hold

  loop

    ln_line_no := NVL(ln_line_no,0) + 1;

    lv_order_rec.header_id := I.Header_Id ;

    lv_order_rec.line_id   :=  NULL;

    lv_order_tbl(ln_line_no) := lv_order_rec ;

  end loop;

  begin

    select hold_id

    into ln_hold_id

    from OE_HOLD_DEFINITIONS

    where upper(name) = upper(lv_hold_name);

  exception

    when no_data_found then

      p_err_msg := 'Hold name is not defined';

    when others then

     p_err_msg := substr(sqlerrm,1,80);

  end;

   lv_comment :=  'Hold applied through API - Trip Number :'||p_trip_number;

  Oe_Holds_Pub.Apply_Holds (

       p_api_version => 1.0,

       p_init_msg_list => Fnd_Api.G_FALSE,

       p_commit => Fnd_Api.G_False,

       p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,

       p_order_tbl => lv_order_tbl ,

       p_hold_id => ln_hold_id,--C_HOLD_FOR.HOLD_ID,

       p_hold_until_date => null,

       p_hold_comment => lv_comment,

       x_return_status => lv_return_status,

       x_msg_count => ln_msg_count,

       x_msg_data => lv_msg_data);

   IF NVL(ln_msg_count,0) > 0 THEN

    FOR j IN 1..ln_msg_count

     LOOP

             Fnd_Msg_Pub.get(p_msg_index           => j,

                                                    p_encoded   => 'F',

                                                    p_data                      => lv_msg_data,

                                                    p_msg_index_out => l_msg_index_out);

     END LOOP;            

   END IF; 

   IF NVL(lv_return_status,'X') = 'S' THEN     

   COMMIT;

   END IF;        

   END;