Fill employee Hierarchy (Approval Hierarchy) -functional setups

PO (Approval Hierarchy)

+=+=+=+=+=+=+=+=+=+=+

Step1 :- Create Employees & Users

step2 :- Create Job & Possition and Assingment

USER     EMP_NAME       JOB                        POSSITION          APPROVAL_GROUP                        APPROVAL_ASSINGMENT

azzez123              azeez                    007.purchasing  007.Clerk                             10000                                    Standarad PO

raghu123             Raghu                   007.purchasing  007.JrMgr            30000                                    Standarad PO

balu123                Balu                       007.purchasing  007.SrMgr           50000                                    Standarad PO

Create Approval Hierarchy:-

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

REL PO AH

007.SrMgr

    007.JrMgr

          007.Clerk

Azeez is having Authority to approve up to 10000 if at all the PO is between 10000 and 30000 then PO has to go to Raghu Automatically.

Similarly 30000 to 50000 PO has go to Balu automatically.

Step3:- Run the Program 'Fill employee Hierarchy'

------------------------------------------------- Over of Process is Ends Here ------------------------------------------------------------

Step1:- Create employee

Switch Responsibility to HRMS

Navigation àWork Structure àJob àDescription à New

Job:- 007.Purchasing

Setp2:- Create Position and assign Job

Navigation àWork Structure à Position àDescription à New

Date Effective Name =

   Position Number =007

   Position Name = Clerk

Organization = REL BG [Business Group]

  Job =007.Purchasing

  Status = Active

Click on hiring Information FTE=1

Step 3:- Create employee assign Job &position

    Job & position

Navigation àPeople àEnter and maintenanceà New

Last  Name = Azeez

Gender = Male

Action = Create Employee

Person type of action = employee

National Identifier =123-0002-09

DOB = 10-JUN-1988

(SAVE)

Click on Assignments àPosition=007.CLERK àCorrection àYes [Job will be populate automatically and save]

Step 4- Create Approval Groups

Navigation àSetup à Approvals àApproval Groups

Name = CLERK_AG

Document Total =10,000

Actual Range =10,000         Low 00.000.0000

                                                High99.999.9999

Step 5:-

Create Approval Assignments

Navigation àSetup àApproval àApproval Assignments

Position = 007.CLEARK

Document Type                                                                Approval Group

AP Prove Standard purchase Orders       CLERK_AG             19-Nov-2013 Save.

Make the Employee has buyer

Navigation àSetup à Personal àBuyers

Add Buyer :-

Azeez (Save)

Step 6:-

Create the user

 Switch Responsibility to System Administrator

Navigation à Security àUser à Define

  User Name = Azeez123

  Password = Welcome1323/Re-enter

Person=Azeez

Responsibility = REL Purchasing CSU.

Repeat above steps for two move employees.

Step 7

Create the Approval hierarchy

Switch Responsibility to HRMS

Navigation àWork Structures àPosition àHierarchy

Name = REL PO AH (Save)

Date =19-Nov-2013 (Save)

Query position Name with

007. SRMANAGER

Sub Ordinates

Name                                                    Name

007.JRMGR                                         Raghu à Click Down

Against the Raghu

Name                                    Name                    Number

007.Clerk                             Azeez                    2

(Save) and Close

Step 8 :-

Assign position hierarchy to standard purchase order

Switch Responsibility to

“REL Purchasing CSU”

Navigation à setup à purchasing àDocument type

Click on update “Standard Purchase order”

Default hierarchy  = REL PO AH

ü  Owner can Approve

[How create PO can approve]

Step9:-

Run the program “Fill Employee Hierarchy”

Navigation àview àrequest àSubmit a request àok.

Name = Fill Employee Hierarcy

OU = REL OU

Submit ànoà fin.

Login with “azeez123”

Create standard purchase order.

Navigation àpurchase order àpurchase order

Supplier  = select supplier

Site = select site

Currency = INR

Lines

Inter = Sony laptop – a029

Qrty=3 price 1000 àshipments

Num=1,org=RCI,ship_to=REL-India CI , UOM=Each, qutity =3, àdistributions.

Qty=3, po charge account =01.000.2225

Close àdistribution & shipments

Azeez can approve on its won because upto 10000 he can be approve.

Crete on more purchase order more than 10,000 less than 30,000

è Po=1010

àapproveàokàit won’t be approve as limit crosses ten thousands we need to forward this one to superior.  

ü  Forward

Below filed with be populated

Automatically.

Forward from = azeez

Path = REL PO AH

Login with “raghu123”

Query with PO=1010 and approve.

Function to calculate Header fill rate in OM

CREATE

OR REPLACE FUNCTION APPS.xx_mother_rep(p_order_number varchar2)


RETURN NUMBER

IS




/*==================================================================================

||

|| PL/SQL Code

||

|| Code Type: function Body

||

|| Filename: APPS.xx_mother_rep

||

|| Author: Swapnil Waydande

||

|| Purpose: Caluculate Header Fill Rate For Mother Report

||

|| Parameters:

|| p_order_number in VARCHAR2

||

|| Version History:

||

|| Name Version Reason for Change Date

|| ------------ --------- --------------------- --------------------

|| Swapnil Waydande 1.0 Creation 12-Dec-2014

==================================================================================*/




l4
NUMBER;

l2
NUMBER;

l3
NUMBER;

l5
VARCHAR2 (20);


CURSOR cur


IS


SELECT SUM (a.reserved_quantity) l2, SUM (a.ordered_quantity) l3--, --a.order_number l5


FROM kvont_compr_so_rpt_line a


WHERE order_number = p_order_number--10043691


GROUP BY a.order_number;

BEGIN




FOR i IN cur


LOOP


if i.l2 != 0


THEN

l4
:=


TO_CHAR ((((i.l2) / (i.l3)


)

*
100


),


'9999999.99'


);



DBMS_OUTPUT.put_line
('fillrate'||i.l2);


ELSE

l4
:= (i.l2) * 100;


END if;


-- DBMS_OUTPUT.put_line ('order_number' || i.l5);

DBMS_OUTPUT.put_line
(l4);


END LOOP;


RETURN l4;

END;

/

How to Develop Custom API

/************** Developing Custom API ****************/

Assume xxkl_fedi_tbl is table with following columns
fedi_id,file_name,doc_id,container_type,container_size,container_status,amount,job_status,creation_date,created_by,last_update_date,last_update

d_by

create or replace package xxkl_fedi_api
-- Specifications
is
-- ************** defining table record type ***************** ---
-- xxkl_fedi_row_ rec xxkl_fei_tbl%towtype;
-- ************** defining custom record type ***************** ---
type xxkl_fedi_type is record(fedi_id number,
                                                 file_name varchar2(8),
                                                 doc_id number,
                                                 container_type varchar2(90),
                                                 container_size number,
                                                 container_status varchar2(1),
                                                 amount number,
                                                 job_status varchar2(1),
                                                 creation_date date,
                                                 created_by  number,
                                                 last_update_date date,
                                                 last_updated_by number
                                                 );
begin
procedure create_fedi_record(xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2,x_fedi_id out number);
proceudre validate_fedi_record(fedi_id in varchar2,xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2);
end xxkl_fedi_api;
/


create or replace package body xxkl_fei_api
-- Implementaion
is
begin
procedure create_fedi_record(xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2,x_fedi_id out number)
is
begin
--new record insert.
INSERT INTO xxkl_fedi_tbl(fedi_id,
                                                 file_name,
                                                 doc_id,
                                                 container_type,
                                                 container_size,
                                                 container_status,
                                                 amount,
                                                 job_status,
                                                 creation_date,
                                                 created_by,
                                                 last_update_date,
                                                 last_updated_by
                                                )
                                VALUES(xxkl_fedi_S.nextval,  --sequence
                                                 xxkl_fedi_rec.file_name,
                                                 xxkl_fedi_rec.doc_id number,
                                                 xxkl_fedi_rec.container_type,
                                                 xxkl_fedi_rec.container_size,
                                                 xxkl_fedi_rec.container_status,
                                                 xxkl_fedi_rec.amount,
                                                 xxkl_fedi_rec.job_status,
                                                 xxkl_fedi_rec.creation_date,
                                                 xxkl_fedi_rec.created_by,
                                                 xxkl_fedi_rec.last_update_date,
                                                 xxkl_fedi_rec.last_updated_by
                                               );
x_return_status  := 'S';
x_fedi_id :=  :currval.fedi_id;
-- implement logic if insert statement fail and display following message.
-- x_return_status  := 'F - insert is failed due not some error message ';
--
end create_fedi_record;
/

proceudre validate_fedi_record(fedi_id in varchar2,xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2)
-- implement
is
begin
null;
end validate_fedi_record;
/
end  xxkl_fedi_api;
/

Composite Types (Record type)

Composite Type:-
=============

Composite type allows you to group the variables. Composite type are two type.

1. Record type. (one row with n columns).
2. Collections or pl/sql tables.


/**************************  Record Type  ******************************/

It allows you to group the variables, all the variables may be same datatype or  differenet datatype.

A)
create or replace procedure xx_po_record_row_type_prod(P_HEADER_ID number)
is
--************** defining table record type *****************---
po_headers_rec PO_HEADERS_ALL%rowtype;
--po_headers_rec is record type.  PO_HEADERS_ALL%rowtype will create the variables with the same name of the columns in the

PO_HEADERS_ALL table and it will group all the variables into po_headers_rec
begin
SELECT * INTO po_headers_rec from PO_HEADERS_ALL where PO_HEADER_ID=P_PO_HEADER_ID;  --only one row allowed.
-- here *(star) must be used as po_headers_rec is type of table_name%rowtype
dbms_output.put_line('PO Number :-   ' || po_header_rec.segment1);
end xx_po_record_row_type_prod;
/

B)
create or replace procedure xx_vendor_record_type_prod(P_VENDOR_ID number)
is
--************** defining custom record type *****************---
type vendor_type is recode(vendor_id number,
                                               segment1 varchar2(40), --vendor number
                                               vendor_name varchar2(240),
                                               vendor_type_lookup_code varchar2(240)
                                               );
vendor_rec vendor_type;
---  vendor_type is user define type with custom columns.
begin
SELECT vendor_id,vendor_num,vendor_name,vendor_type_lookup_code INTO vendor_rec FROM ap_suppliers WHERE

venoder_id=p_vendor_id;   --only one row allowed.
dbms_output.put_line('Supplier Num:-   ' || vendor_rec.segment1);
dbms_output.put_line('Supplier Name:-   ' || vendor_rec.vendor_name);
end xx_vendor_record_type_prod;
/

Cancel Back orders

1. Code for Cancel Back orders [OM] with the help of standerd API(Ineer loop Cursor).
   - Cancel partial quntity and back order foe specific org.

CREATE

OR REPLACE PACKAGE BODY APPS.xxwms_oe_backorder

AS

PROCEDURE kvwms_cancel_orders (

errbuf_out
OUT VARCHAR2,

retcode_out
OUT VARCHAR2,

p_order_number
IN VARCHAR2,

p_org_id
IN NUMBER,

p_email_id
IN VARCHAR2
 
 

)

AS

v_qty

NUMBER DEFAULT 0;

l_user_id

NUMBER;

l_resp_id

NUMBER;

l_appl_id

NUMBER;

l_return_status

VARCHAR2 (1000);

 

--INPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_in

oe_order_pub.header_rec_type;

l_line_tbl_in

oe_order_pub.line_tbl_type;

 

 

--l_header_rec1:=OE_ORDER_PUB.G_MISS_HEADER_REC;

 

-- t_line_tbl1(1) := OE_ORDER_PUB.G_MISS_LINE_REC;

 

--l_msg_data VARCHAR2 (1000);

l_action_request_tbl_in

oe_order_pub.request_tbl_type;

 

--OUTPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_out

oe_order_pub.header_rec_type;

l_line_tbl_out

oe_order_pub.line_tbl_type;

l_header_val_rec_out

oe_order_pub.header_val_rec_type;

l_header_adj_tbl_out

oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl_out

oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl_out

oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl_out

oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl_out

oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl_out

oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl_out

oe_order_pub.header_scredit_val_tbl_type;

l_line_val_tbl_out

oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out

oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl_out

oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl_out

oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl_out

oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl_out

oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl_out

oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl_out

oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl_out

oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl_out

oe_order_pub.lot_serial_val_tbl_type;

l_action_request_tbl_out

oe_order_pub.request_tbl_type;

l_msg_index_out

NUMBER (10);

l_chr_program_unit_name

VARCHAR2 (100);

l_ret_status

VARCHAR2 (1000) := NULL;

l_msg_count

NUMBER := 0;

l_msg_data

VARCHAR2 (20000);

l_api_version

NUMBER := 1.0;

v_audit

NUMBER;

v_out_file_name

VARCHAR2 (400);

v_msg_text

VARCHAR2 (4000);

v_send_email

NUMBER;

v_error_msg

VARCHAR2 (4000);

 

CURSOR get_order_details (p_order_number IN VARCHAR2, p_org_id IN NUMBER

 

 

-- ,p_customer_number IN VARCHAR2

)

 

 

IS

 

SELECT DISTINCT oh.order_number order_number, ol.line_number,

ol

.line_id, wdd.released_status, hp.party_name,

hp

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status in ('B','S') --changed added 'S' Status

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

       

--Added logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

 

UNION

 

SELECT DISTINCT oh.order_number order_number, ol.line_number,

ol

.line_id, wdd.released_status, hp.party_name,

hp

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

CURSOR b (p_1 IN VARCHAR2, p_2 IN NUMBER)

IS

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

,

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

AND wdd.released_status = 'B'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1

-- i.order_number --10060173

-- NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

--Aded logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

UNION

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

,

mtl_system_items msi

,

mtl_parameters mp

,

wsh_delivery_details wdd

,

hz_cust_accounts hca

,

hz_parties hp

,

hz_party_sites hps

,

hz_cust_acct_sites_all hcasa

,

hz_cust_site_uses_all hcsua

,

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

--AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1 --10060173

--NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2 --p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

BEGIN

DBMS_OUTPUT.put_line

('start 1');

/* SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = 'RPADHI'; */

SELECT fcr.requested_by user_id

INTO l_user_id

--,fcr.responsibility_id,

--fcr.responsibility_application_id

FROM apps.fnd_concurrent_requests fcr

WHERE request_id = fnd_global.conc_request_id;

fnd_file.put_line (fnd_file.LOG, 'User ID : ' || l_user_id);

v_out_file_name

:= 'o' || fnd_global.conc_request_id || '.out';

SELECT responsibility_id, application_id

INTO l_resp_id, l_appl_id

FROM fnd_responsibility_vl

WHERE responsibility_name LIKE 'KV OM Inquiry';

--added responsibility name on 9-may by kamal

-- INITIALIZE ENVIRONMENT

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

fnd_file.put_line (fnd_file.LOG, 'Responsibility ID : ' || l_resp_id);

fnd_file.put_line (fnd_file.LOG, 'Application ID : ' || l_appl_id);

--v_msg_text := 'Order_number | Line_number | Line_id| Status | Message ';

--DBMS_OUTPUT.put_line ('User_id'||l_user_id);

v_msg_text

:=

RPAD ('Order_number', 15)

||

'|'

||

RPAD ('Line_number', 12)

||

'|'

||

RPAD ('Line_id', 10)

||

'|'

||

RPAD ('Status', 7)

||

'|'

||

'Message';

v_send_email

:= 0;

fnd_file.put_line (fnd_file.output, v_msg_text);

DBMS_OUTPUT.put_line

(v_msg_text);

FOR i IN get_order_details (p_order_number, p_org_id)

LOOP

--**************************************************************************************************************************

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN ('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = i.line_id

AND wdd.source_code = 'OE'; --added on 15 may

--DBMS_OUTPUT.put_line ('Stage2');

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1, 'Quantity not Available' || SQLERRM);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

/********** inner for loop starts here **********/

FOR j IN b (i.order_number, i.organization_id)

LOOP

BEGIN

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN

('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = j.line_id

AND wdd.source_code = 'OE'; --added on 15 may

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1,

'Quantity not Available' || SQLERRM

);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

l_line_tbl_in

(1).line_id := j.line_id;

-- l_line_tbl_in (1).FLOW_STATUS_CODE := 'CANCELLED';

l_line_tbl_in

(1).change_reason := 'KVBOCAN';

--Administrative Reason

l_line_tbl_in

(1).change_comments := 'Cancel Order Line';

l_line_tbl_in

(1).operation := oe_globals.g_opr_update;

-- Mandatory Operation to Pass

oe_msg_pub.delete_msg;

oe_order_pub

.process_order

(p_api_version_number => l_api_version,

p_init_msg_list

=> fnd_api.g_false,

p_return_values

=> fnd_api.g_false,

p_action_commit

=> fnd_api.g_false,

p_line_tbl

=> l_line_tbl_in,

x_header_rec

=> l_header_rec_out,

x_header_val_rec

=> l_header_val_rec_out,

x_header_adj_tbl

=> l_header_adj_tbl_out,

x_header_adj_val_tbl

=> l_header_adj_val_tbl_out,

x_header_price_att_tbl

=> l_header_price_att_tbl_out,

x_header_adj_att_tbl

=> l_header_adj_att_tbl_out,

x_header_adj_assoc_tbl

=> l_header_adj_assoc_tbl_out,

x_header_scredit_tbl

=> l_header_scredit_tbl_out,

x_header_scredit_val_tbl

=> l_header_scredit_val_tbl_out,

x_line_tbl

=> l_line_tbl_out,

x_line_val_tbl

=> l_line_val_tbl_out,

x_line_adj_tbl

=> l_line_adj_tbl_out,

x_line_adj_val_tbl

=> l_line_adj_val_tbl_out,

x_line_price_att_tbl

=> l_line_price_att_tbl_out,

x_line_adj_att_tbl

=> l_line_adj_att_tbl_out,

x_line_adj_assoc_tbl

=> l_line_adj_assoc_tbl_out,

x_line_scredit_tbl

=> l_line_scredit_tbl_out,

x_line_scredit_val_tbl

=> l_line_scredit_val_tbl_out,

x_lot_serial_tbl

=> l_lot_serial_tbl_out,

x_lot_serial_val_tbl

=> l_lot_serial_val_tbl_out,

x_action_request_tbl

=> l_action_request_tbl_out,

x_return_status

=> l_ret_status,

x_msg_count

=> l_msg_count,

x_msg_data

=> l_msg_data

);

l_msg_data

:= NULL;

v_error_msg

:= NULL;

IF l_ret_status <> 'S'

THEN

FOR iindx IN 1 .. l_msg_count

LOOP

-- l_msg_data := l_msg_data || ' ' || oe_msg_pub.get (iindx);

oe_msg_pub.get (p_msg_index => iindx,

p_encoded

=> fnd_api.g_false,

p_data

=> l_msg_data,

p_msg_index_out

=> l_msg_index_out

);

v_error_msg

:= l_msg_data || ' ' || l_msg_data;

fnd_file.put_line (fnd_file.LOG, 'in error loop ');

-- DBMS_OUTPUT.put_line ('in error loop ');

COMMIT;

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Failed *****'

);

--DBMS_OUTPUT.put_line ('Backorder Failed ');

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

--DBMS_OUTPUT.put_line ('Sales Order => ' || j.order_number);

-- fnd_file.put_line (fnd_file.LOG,

-- 'Line Number => ' || j.line_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| j

.line_id

||

' Line Cancelation Failed'

);

--DBMS_OUTPUT.put_line ('Line ID=> ' || j.line_id);

--DBMS_OUTPUT.put_line ('Return Status: ' || l_ret_status);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

v_send_email

:= 1;

v_error_msg

:= REPLACE (v_error_msg, CHR (13), '-');

v_error_msg

:= REPLACE (v_error_msg, CHR (10), ' ');

v_msg_text

:=

RPAD (j.order_number, 15)

||

'|'

-- || RPAD (j.line_number, 12)

||

'|'

||

RPAD (j.line_id, 10)

||

'|'

||

RPAD (l_ret_status, 7)

||

'|'

|| v_error_msg

;

fnd_file.put_line (fnd_file.output, v_msg_text);

-- DBMS_OUTPUT.put_line (v_msg_text);

ELSE

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Sucess *****'

);

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

-- DBMS_OUTPUT.put_line

-- ( 'sucess back order:-Sales Order => '

-- || j.order_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line Number => ' || j.line_id

);

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| i

.line_id

||

' Line Cancelled Successfully'

);

-- DBMS_OUTPUT.put_line ('sucess:-Line ID=> ' || j.line_id);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

DBMS_OUTPUT.put_line

('Return Status: ' || l_ret_status);

-- fnd_file.put_line(fnd_file.log,'Error Message: ' || l_msg_data);

END IF;

COMMIT;

END;

COMMIT;

--**************************************************************************************************************************

END LOOP;

END LOOP;

END xxwms_cancel_orders;