Oracle Sale Order Loading Process By Using API oe_order_pub
Purpose : To load the data
from staging to oracle
CREATE OR REPLACE PROCEDURE APPS.xxbilt_ebs_sale_ord_creation_n (
errbuf
VARCHAR2,
retcode
VARCHAR2
-- p_ordernumber VARCHAR2 -- by hs
)
IS
--DECLARE
l_h_order_source VARCHAR2 (2000);
l_api_version_number NUMBER := 1.0;
error_msgs VARCHAR2 (2000);
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
p_itmval VARCHAR2 (100);
p_Tax_Ver VARCHAR2 (100);
/*****************parameters****************************************************/
l_org NUMBER; -- := 204;
-- 109;
l_no_orders NUMBER; ---:= 1;
l_user NUMBER := 1558;
--2808;--
l_resp NUMBER := 53932;
--******
-- 53932 Order Management Super User RETAIL
NORTH
--53933 Order Management
Super User RETAIL SOUTH
--56661 Order Management
Super User RETAIL WEST
--53931 Order Management
Super User RETAIL EAST
-- 50812;--
l_appl NUMBER := 660;
l_debug_level NUMBER := 3;
/*****************input variables
for pro2cess_order api*************************/
l_header_rec oe_order_pub.header_rec_type;
--l_line_tbl
oe_order_pub.line_tbl_type;
l_line_tbl oe_order_pub.line_tbl_type;
--Price Adjustment record
l_action_request_tbl oe_order_pub.request_tbl_type;
l_line_adj_tbl oe_order_pub.line_adj_tbl_type;
/*****************out
variables for process_order api***************************/
l_header_rec_out oe_order_pub.header_rec_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_tbl_out oe_order_pub.line_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 NUMBER;
l_data VARCHAR2 (2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2 (200);
l_h_order_type VARCHAR2 (2000);
l_h_customer_name VARCHAR2 (2000);
l_loop_count NUMBER;
l_h_salesrep_name NUMBER;
l_h_price_list NUMBER;
l_h_ship_from_org_name NUMBER;
l_ship_to_org_id NUMBER;
l_price_list NUMBER;
l_cust_po_number NUMBER;
l_payment_term NUMBER;
l_ord_date DATE;
l_item_id NUMBER;
l_bill_to_org_id NUMBER;
l_list_line NUMBER;
l_list_hdr NUMBER;
l_opr NUMBER;
l_list_price NUMBER;
l_price_adjust NUMBER;
temp NUMBER := 0;
l_order_num VARCHAR2 (80 BYTE);
CURSOR c1
IS
SELECT DISTINCT *
FROM xxbilt_web_sales_order_hdr
WHERE 1 = 1 --p_status IS NULL
-- AND ordernumber IN
('100000000000592')
AND (p_status IN ('U') OR p_status IS NULL)
AND ordernumber NOT IN (SELECT order_number
FROM oe_order_headers_all)
AND customercode LIKE 'RN%';
-- AND ordernumber NOT IN (
-- SELECT DISTINCT
ordernumber
-- FROM
xxbilt_web_sales_order_lines
-- WHERE
vatrate IN
--
('12.5', '5.25', '13.13', '14.5',
--
'13.5', '5.5'));
--'34501101';
--p_ordernumber;-- By HS
CURSOR c2 (p_ordnum NUMBER)
IS
SELECT DISTINCT *
FROM xxbilt_web_sales_order_lines --By Hs
WHERE 1 = 1
-- AND ordernumber IN
('100000000000592')
AND ordernumber NOT IN (SELECT order_number
FROM oe_order_headers_all)
AND (p_status IN ('U') OR p_status IS NULL)
-- AND ordernumber NOT IN (
-- SELECT DISTINCT
ordernumber
-- FROM
xxbilt_web_sales_order_lines
-- WHERE
vatrate IN
--
('12.5', '5.25', '13.13', '14.5',
--
'13.5', '5.5'))
AND ordernumber = p_ordnum;
-- p_status IS NULL
-- and ordernumber='200000000000004'
-- AND ordernumber IN (
-- SELECT ordernumber
-- FROM
xxbilt_web_sales_order_hdr
-- WHERE 1 = 1 --p_status IS
NULL
-- AND ordernumber NOT IN
(SELECT order_number
--
FROM oe_order_headers_all));
BEGIN
DBMS_OUTPUT.ENABLE (2000000);
/* BEGIN
fnd_file.put_line (fnd_file.LOG,
'begin');
SELECT COUNT (1)
INTO l_no_orders
FROM xxbilt_web_sales_order_lines
WHERE ordernumber =
c1.order_number;
--'34501101';
END;*/ --By Hs
IF (l_debug_level > 0)
THEN
l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
oe_msg_pub.initialize;
END IF;
fnd_global.apps_initialize (l_user, l_resp, l_appl);
mo_global.init ('ONT');
FOR i IN c1
LOOP
fnd_file.put_line (fnd_file.LOG,
'WebPoratl Order
Number-->'
|| i.ordernumber
|| ' For the Customer
Code-->'
|| i.customercode
);
-- fnd_file.put_line (fnd_file.LOG, 'i loop
started' || i.ordernumber)
DBMS_OUTPUT.put_line ('i loop started' || i.ordernumber);
--*************** ITEM
VALIDATION **********
BEGIN
SELECT DISTINCT DECODE (attribute10, NULL, 'NO_ITEM', attribute10)
val_item
INTO p_itmval
FROM xxbilt_web_sales_order_lines a, mtl_system_items_b b
WHERE ordernumber = i.ordernumber
-- and
b.organization_id=110
AND a.itemcode = b.attribute10(+)
AND b.attribute10 IS NULL;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Item Mapped');
END;
--*************** ITEM
VALIDATION **********
--********** TAX VALIDATION
************
BEGIN
SELECT DISTINCT decode (ebs_tax_id , null,'NO_TAX' , ebs_tax_id ) TAX
INTO p_tax_Ver
FROM xxbilt_web_sales_order_lines a, bilt.xx_web_taxes_tl b
WHERE a.taxtype = b.web_tax_type(+)
AND a.vatrate = b.web_tax_per(+)
AND a.shopcode = b.web_shop_code(+)
and ordernumber = i.ordernumber
and ebs_tax_id is null ;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'TAX Mapped');
END;
--********** TAX VALIDATION
************
BEGIN
SELECT DISTINCT order_source_id
INTO l_h_order_source
FROM oe_order_sources
WHERE NAME = 'Online';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'order source name is
failed');
END;
BEGIN
/*SELECT
a.transaction_type_id, sh.operating_unit
INTO l_h_order_type, l_org
FROM oe_transaction_types_all a,
oe_transaction_types_tl tl,
bilt.xx_web_shops_tl sh,
xxbilt_web_sales_order_hdr h
WHERE a.transaction_type_id =
tl.transaction_type_id
AND a.transaction_type_id =
sh.order_type_id
AND h.shopcode = sh.shopcode
AND h.ordernumber = i.ordernumber;*/
SELECT DISTINCT a.order_type_id, b.operating_unit -- SHAGUL HARD CODE
INTO l_h_order_type, l_org
FROM hz_cust_site_uses_all a,
org_organization_definitions
b
WHERE attribute2 = i.ship_to
AND a.warehouse_id = b.organization_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error:Order type mapping
detail is not available for the customer account number - Also Check the
warehouse'
|| i.customercode
);
error_msgs :=
'Error:Order type mapping
detail is not available for the customer account number - Also Check the
warehouse';
UPDATE xxbilt_web_sales_order_hdr
SET error_msg =
'Error:Order type mapping
detail is not available for the customer account number - Also Check the
warehouse'
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_lines
SET error_msg =
'Error:Order type mapping
detail is not available for the customer account number - Also Check the
warehouse'
WHERE ordernumber = i.ordernumber;
-- AND itemcode = j.itemcode ;
END;
BEGIN
SELECT DISTINCT cust_account_id
INTO l_h_customer_name
FROM hz_parties hp,
hz_cust_accounts hca,
xxbilt_web_sales_order_hdr h
WHERE hp.party_id = hca.party_id
AND h.customercode = hca.account_number
AND hca.account_number = i.customercode
AND hp.status = 'A'
AND hca.status = 'A'
AND h.ordernumber = i.ordernumber;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error: Customer name
is not available - '
|| i.customercode
);
END;
BEGIN
SELECT DISTINCT hcsua.site_use_id
INTO l_ship_to_org_id
FROM hz_locations hl,
hz_cust_site_uses_all
hcsua,
hz_cust_acct_sites_all
hcasa,
hz_party_sites hps,
hz_cust_accounts
hca --- Shagul Hard Coded
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hca.cust_account_id = hcasa.cust_account_id
AND hca.account_number = i.customercode
AND hcsua.attribute2 = i.ship_to;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error: Customer
Shipping detail is not available - '
|| i.customercode
);
error_msgs :=
'Error: Customer
Shipping detail is not available - ';
END;
BEGIN
SELECT DISTINCT hcsua.site_use_id
INTO l_bill_to_org_id
FROM hz_locations hl,
hz_cust_site_uses_all
hcsua,
hz_cust_acct_sites_all
hcasa,
hz_party_sites hps,
hz_cust_accounts hca
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.site_use_code = 'BILL_TO'
AND hca.cust_account_id = hcasa.cust_account_id
AND hca.account_number = i.customercode
and hcsua.attribute1=i.bill_to;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error: Customer
Billing detail is not available - '
|| i.customercode
);
error_msgs := 'Error: Customer Billing detail
is not available - ';
END;
BEGIN
SELECT DISTINCT hcsua.primary_salesrep_id
INTO l_h_salesrep_name
FROM hz_locations hl,
hz_cust_site_uses_all
hcsua,
hz_cust_acct_sites_all
hcasa,
hz_party_sites hps,
hz_cust_accounts hca
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hca.cust_account_id = hcasa.cust_account_id
AND hca.account_number = i.customercode
AND hcsua.primary_salesrep_id IS NOT NULL;
-- fnd_file.put_line (fnd_file.LOG,
-- 'price list
id-------->' || l_price_list
-- );
-- fnd_file.put_line (fnd_file.LOG,
-- 'sales person
id-------->' || l_h_salesrep_name
-- );
EXCEPTION
WHEN OTHERS
THEN
--fnd_file.put_line
(fnd_file.LOG, 'Price list is not
available - ' || i.CUSTOMERCODE);
fnd_file.put_line (fnd_file.LOG,
'Error: Sales person is
not available - '
|| i.customercode
);
error_msgs := 'Error: Sales person is not
available - ';
UPDATE xxbilt_web_sales_order_hdr
SET error_msg = 'Error: Sales person is
not available'
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_lines
SET error_msg = 'Error: Sales person is
not available'
WHERE ordernumber = i.ordernumber;
-- AND itemcode = j.itemcode ;
END;
BEGIN
SELECT DISTINCT customerponumber
INTO l_cust_po_number
FROM xxbilt_web_sales_order_hdr
WHERE ordernumber = i.ordernumber; --'3450110' ;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error: customer po
number failed'
);
END;
BEGIN
SELECT DISTINCT hcsua.price_list_id
INTO l_h_price_list
FROM hz_locations hl,
hz_cust_site_uses_all
hcsua,
hz_cust_acct_sites_all
hcasa,
hz_party_sites hps,
hz_cust_accounts hca
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.site_use_code = 'SHIP_TO'
AND hca.cust_account_id = hcasa.cust_account_id
AND hca.account_number = i.customercode --'RN98700000';
AND hcsua.price_list_id IS NOT NULL;
fnd_file.put_line (fnd_file.LOG, 'price list---->' || l_h_price_list);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error: Price list is not available - '
|| i.customercode
);
error_msgs := 'Error: Price list is not available - ';
UPDATE xxbilt_web_sales_order_hdr
SET error_msg = 'Error: Price list is not available -'
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_lines
SET error_msg = 'Error: Price list is not available -'
WHERE ordernumber = i.ordernumber;
-- AND orderlinenumber =
j.ORDERLINENUMBER;
END;
BEGIN
SELECT DISTINCT hcsua.payment_term_id
INTO l_payment_term
FROM hz_locations hl,
hz_cust_site_uses_all
hcsua,
hz_cust_acct_sites_all
hcasa,
hz_party_sites hps,
hz_cust_accounts hca
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id
AND hcsua.site_use_code = 'BILL_TO'
AND hca.cust_account_id = hcasa.cust_account_id
AND hca.account_number = i.customercode; --'RN98700000';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error: Payment term is not available
in the customer bill to '
|| i.customercode
);
error_msgs :=
'Error: Payment term is not
available in the customer bill to ';
END;
BEGIN
SELECT DISTINCT TO_CHAR (TO_DATE (SUBSTR (orderdate, 1, 19),
'YYYY-MM-DD HH24:MI:SS'
),
'DD-MON-YYYY'
) "ORDERDATE"
-- Sivakumaran orderdate
problem
INTO l_ord_date
FROM xxbilt_web_sales_order_hdr
WHERE ordernumber = i.ordernumber;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error: Order date is not
available for the order number '
|| i.ordernumber
);
END;
BEGIN
SELECT DISTINCT a.organization_id
INTO l_h_ship_from_org_name
FROM org_organization_definitions
a,
bilt.xx_web_shops_tl b
WHERE a.organization_code = b.inventory_org_code
AND b.shopcode = i.shopcode;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error: Inventory organisation
mapping was not done, shop code number - '
|| i.shopcode
);
error_msgs :=
'Error: Inventory organisation
mapping was not done, shop code number - ';
END;
-- l_header_rec.DELETE;
-- l_line_tbl.DELETE;
/*****************initialize
header record******************************/
/*****************initialize
line record********************************/
IF p_itmval IS NULL
THEN
If p_Tax_Ver is null
then
/************ LINE
VALIDATIONS *******************/
FOR j IN c2 (i.ordernumber)
LOOP
temp := temp + 1;
----FOR i IN 1 .. l_no_orders
--LOOP
fnd_file.put_line (fnd_file.LOG,
'THE ORDER LINE NUMBER
IS '
|| j.orderlinenumber
);
fnd_file.put_line (fnd_file.LOG, 'loop index no is ' || temp);
l_item_id := NULL;
BEGIN
SELECT DISTINCT c.inventory_item_id
INTO l_item_id
FROM bilt.xx_web_shops_tl a,
xxbilt_web_sales_order_lines b,
mtl_system_items_b c
WHERE a.shopcode = b.shopcode
AND b.itemcode = j.itemcode --B.ORDER ITEM
AND b.itemcode = c.attribute10
AND a.organization_id = c.organization_id
AND b.ordernumber = j.ordernumber; --'34501101'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Item / attribute
mapping is not availble for the webportal Item number '
|| j.itemcode
);
UPDATE xxbilt_web_sales_order_hdr
SET error_msg =
'Item / attribute
mapping is not availble for the webportal Item number '
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_lines
SET error_msg =
'Item / attribute mapping
is not availble for the webportal Item number '
|| j.orderlinenumber
|| 'and'
|| j.itemcode
WHERE ordernumber = i.ordernumber
AND orderlinenumber = j.orderlinenumber;
END;
IF l_item_id IS NULL
THEN
ROLLBACK; -- shagul
EXIT;
END IF;
BEGIN
SELECT DISTINCT a.price_list_line_id, b.list_header_id,
b.operand, a.list_price
INTO l_list_line, l_list_hdr,
l_opr, l_list_price
FROM
qp_price_list_lines_v a, qp_list_lines b
WHERE price_list_id = l_h_price_list --17014
AND a.inventory_item_id = l_item_id
AND a.price_list_line_id = b.list_line_id
AND b.end_date_active IS NULL;
fnd_file.put_line (fnd_file.LOG,
'LIST PRICE IS --' || l_list_price
);
--*** ADJISSUE
-- IF round(l_list_price) <>
round(j.salesprice)
-- THEN
-- l_price_adjust :=
l_list_price - j.salesprice;
-- fnd_file.put_line
(fnd_file.LOG,
-- 'PRICE
ADJUSTMENT AMOUNT IS '
-- ||
l_price_adjust
-- );
-- DBMS_OUTPUT.put_line
('Adjusted Price ' || l_price_adjust);
-- END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('price list header id
and line id failed');
error_msgs := 'price list header id
and line id failed';
END;
BEGIN
SELECT DISTINCT (l_list_price - j.salesprice) adj_prc
INTO l_price_adjust
FROM xxbilt_web_sales_order_lines
WHERE itemcode = j.itemcode
AND ordernumber = j.ordernumber
AND orderlinenumber = j.orderlinenumber;
DBMS_OUTPUT.put_line
( 'ADJUST_PRICE:'
||
l_price_adjust
|| j.itemcode
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
('***PRICE9**');
END;
--**** ADJISSUE
l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************populate
required attributes **********************************/
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_type_id := l_h_order_type;
l_header_rec.ordered_date := l_ord_date;
l_header_rec.request_date := l_ord_date;
l_header_rec.sold_to_org_id := l_h_customer_name;
l_header_rec.ship_to_org_id := l_ship_to_org_id;
l_header_rec.invoice_to_org_id := l_bill_to_org_id;
l_header_rec.order_source_id := l_h_order_source;
l_header_rec.booked_flag := 'Y';
l_header_rec.price_list_id := l_h_price_list;
l_header_rec.cust_po_number := l_cust_po_number;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code
:= 'INR';
l_header_rec.flow_status_code := 'BOOKED';
l_header_rec.booked_date := SYSDATE;
l_header_rec.salesrep_id := l_h_salesrep_name;
l_header_rec.payment_term_id := l_payment_term;
l_header_rec.attribute5 := i.ordernumber;
l_header_rec.cust_po_number := i.customerponumber;
l_header_rec.order_number := i.ordernumber;
l_header_rec.attribute3 := i.bookedusername;
l_header_rec.attribute4 := i.department;
l_header_rec.attribute5 := i.booked_email_id;
l_header_rec.attribute6 := i.remarks;
/*****************initialize
action request record*************************************/
l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
-- l_action_request_tbl (1) :=
oe_order_pub.g_miss_request_rec;
l_action_request_tbl (1).request_type := oe_globals.g_book_order;
l_action_request_tbl (1).entity_code := oe_globals.g_entity_header;
l_line_tbl (temp) := oe_order_pub.g_miss_line_rec;
l_line_tbl (temp).operation := oe_globals.g_opr_create;
l_line_tbl (temp).inventory_item_id := l_item_id; -- CHECK
QUERY
l_line_tbl (temp).request_date := l_ord_date; --158002;
l_line_tbl (temp).ordered_quantity := j.orderquantity;
l_line_tbl (temp).org_id := l_org; -- 109;
l_line_tbl (temp).shipment_number := 1;
l_line_tbl (temp).line_number := j.orderlinenumber;
l_line_tbl (temp).tax_code := 'LOCRATE';
l_line_tbl (temp).schedule_status_code := 'SCHEDULED';
l_line_tbl (temp).schedule_ship_date := l_ord_date + 2;
--l_line_tbl
temp.calculate_price_flag := 'y';
--l_line_tbl.sold_to_org_id
:= 59658;
l_line_tbl (temp).ship_from_org_id :=
l_h_ship_from_org_name;
--621;
--l_h_ship_from_org_name; --- 621;
l_line_tbl (temp).invoice_to_org_id := l_bill_to_org_id;
l_line_tbl (temp).order_quantity_uom := j.unitofmeasure;
l_line_tbl (temp).unit_list_price := l_list_price;
/*************adjustments
*****************************************/
l_line_adj_tbl (temp) := oe_order_pub.g_miss_line_adj_rec;
l_line_adj_tbl (temp).operation := oe_globals.g_opr_create;
l_line_adj_tbl (temp).list_header_id := 603307;
-- l_list_hdr;
l_line_adj_tbl (temp).list_line_id := 808158; --l_list_line;
l_line_adj_tbl (temp).change_reason_code := 'MANUAL';
l_line_adj_tbl (temp).change_reason_text :=
'Manually
applied adjustments';
l_line_adj_tbl (temp).operand := l_opr; --2000;
l_line_adj_tbl (temp).pricing_phase_id := 2;
-- l_line_adj_tbl
(temp).adjusted_amount := l_price_adjust;--3.5; shagul ---2;
l_line_adj_tbl (temp).operand := l_price_adjust;
--3.5; shagul
l_line_adj_tbl (temp).updated_flag := 'Y';
l_line_adj_tbl (temp).applied_flag := 'Y';
l_line_adj_tbl (temp).line_index := temp;
END LOOP;
Else
fnd_file.put_line
(fnd_file.LOG,
'Wrong Mapping For Tax -->'
|| i.ordernumber
);
Rollback ;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = 'E',
error_msg =
'Wrong Mapping For Tax'
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = 'E',
error_msg =
'Wrong Mapping For Tax'
WHERE ordernumber = i.ordernumber;
COMMIT;
EXIT;
END IF; --Sudha
ELSE
fnd_file.put_line
(fnd_file.LOG,
'Item Mapping validation Was
Failed For the WebPoratl Order Number-->'
|| i.ordernumber
);
ROLLBACK;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = 'E',
error_msg =
'Item Mapping validation Was
Failed For the WebPoratl Order Number'
WHERE ordernumber = i.ordernumber;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = 'E',
error_msg =
'Item Mapping validation Was
Failed For the WebPoratl Order Number'
WHERE ordernumber = i.ordernumber;
COMMIT;
EXIT;
END IF;
/*****************callto
process order api*********************************/
oe_order_pub.process_order
(p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
p_line_adj_tbl => l_line_adj_tbl
-- out variables
,
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_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = fnd_api.g_ret_sts_success
THEN
IF (l_debug_level > 0)
THEN
fnd_file.put_line (fnd_file.LOG,
'Sales Order
Successfully Created'
);
END IF;
-- COMMIT; -- shagul 1
ELSE
IF (l_debug_level > 0)
THEN
fnd_file.put_line
(fnd_file.LOG,
'Failed to Create Sales Order please
check the Error '
|| l_return_status
);
END IF;
ROLLBACK;
END IF;
IF l_return_status <> 'S'
THEN
UPDATE xxbilt_web_sales_order_lines
SET p_status = l_return_status,
error_msg = error_msgs
WHERE ordernumber = i.ordernumber;
-- AND orderlinenumber =
j.orderlinenumber;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = l_return_status,
error_msg = error_msgs
WHERE ordernumber = i.ordernumber;
ELSE
UPDATE xxbilt_web_sales_order_lines
SET p_status = 'S'
WHERE ordernumber = i.ordernumber;
-- AND orderlinenumber = j.orderlinenumber;
UPDATE xxbilt_web_sales_order_hdr
SET p_status = 'S'
WHERE ordernumber = i.ordernumber;
END IF;
COMMIT;
--
shagul 2
--- end loop;
-- END LOOP;
-- display return status
flags
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ( 'Process Order Return Status is: ========>'
||
l_return_status
);
fnd_file.put_line (fnd_file.LOG,
'Process Order msg data
is: ===========>'
|| l_msg_data
);
fnd_file.put_line (fnd_file.LOG,
'Process Order Message Count
is:=======>'
|| l_msg_count
);
fnd_file.put_line (fnd_file.LOG,
'Sales Order Created
is:===============>'
|| TO_CHAR (l_header_rec_out.order_number)
);
DBMS_OUTPUT.put_line ( 'Booked Flag for the Sales Order is:======>'
||
l_header_rec_out.booked_flag
);
DBMS_OUTPUT.put_line ( 'Header_id for the Sales Order is:========>'
||
l_header_rec_out.header_id
);
DBMS_OUTPUT.put_line ( 'Flow_Status_Code For the Sales Order is=>:'
||
l_header_rec_out.flow_status_code
);
END IF;
-- display error msgs
IF (l_debug_level > 0)
THEN
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_data,
p_msg_index_out => l_msg_index
);
fnd_file.put_line (fnd_file.LOG, 'message is:' || l_data);
fnd_file.put_line (fnd_file.LOG,
'message index is:' || l_msg_index
);
END LOOP;
END IF;
-- IF (l_debug_level > 0)
-- THEN
-- fnd_file.put_line (fnd_file.LOG,
'Debug = ' || oe_debug_pub.g_debug);
-- fnd_file.put_line (fnd_file.LOG,
-- 'Debug Level = '
-- || TO_CHAR (oe_debug_pub.g_debug_level)
-- );
---- fnd_file.put_line (fnd_file.LOG,
---- 'Debug File ='
---- ||
oe_debug_pub.g_dir
---- || '/'
---- ||
oe_debug_pub.g_file
---- );
-- oe_debug_pub.debug_off;
-- END IF;
temp := 1 + temp;
BEGIN
xxbilt_tax_load (l_header_rec_out.header_id);
END;
-- Begin
-- Order_Prc_Adj (i.ordernumber);
-- End;
-- BEGIN
-- xx_adj_ord_web (i.ordernumber);
-- END;
DBMS_OUTPUT.put_line (i.ordernumber);
END LOOP;
COMMIT;
END;
/
Comments
Post a Comment