CREATE OR REPLACE PACKAGE mot_j2j_sales_order_conv_pkg
AS
--/****************************************************************************************************/
--/* */
--/* Header : mot_j2j_sales_order_conv_pkg.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : This package is used to create Sales Orders */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTOM_SALES_ORDERS_TBL, OC.MOTOM_SALES_ORDERS_BAD_TBL */
--/* API's/Open Interfaces: */
--/* OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL */
--/* */
--/* Value Sets: */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* Modification History : */
--/* 28-Jul-2011 v0.1 Initial Creation */
--/* */
--/****************************************************************************************************/
CURSOR interface_cur
IS
SELECT *
FROM MOTC.MOTOM_SALES_ORDERS_TBL
WHERE process_flag = 'I'
OR process_flag IS NULL;
error_flag VARCHAR2(50);
v_err_buf VARCHAR2(50);
v_ret_code NUMBER;
v_customer_number NUMBER;
v_order_type_name VARCHAR2(100);
v_process_flag VARCHAR2(100);
v_hdr_err_flag VARCHAR2(50) := 'N';
v_err_msg VARCHAR2(1000):= NULL;
v_user_login VARCHAR2(100) := FND_PROFILE.VALUE('USER_ID');
PROCEDURE validation_insertion_proc;
PROCEDURE main_validation_proc(errbuf OUT VARCHAR2,retcode OUT NUMBER);
END mot_j2j_sales_order_conv_pkg;
/
========================================================package body=================================
--/****************************************************************************************************/
--/* */
--/* Header : mot_j2j_sales_order_conv_pkg.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : Validating whether Customer and Order Type exist or not */
--/* and based on that inserting Data into Order Interface Tables from Staging Table */
--/* by calling two procedures main_validation_proc,validation_insertion_proc. */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTOM_SALES_ORDERS_TBL, OC.MOTOM_SALES_ORDERS_BAD_TBL */
--/* API's/Open Interfaces: */
--/* OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL */
--/* */
--/* Value Sets: */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* Modification History : */
--/* 28-Jul-2011 v0.1 Initial Creation */
--/* */
--/****************************************************************************************************/
CREATE OR REPLACE PACKAGE BODY mot_j2j_sales_order_conv_pkg
AS
/* This Procedure is doing validation at header level as well as line level and then inserting
data into interface tables */
PROCEDURE validation_insertion_proc
AS
v_sold_to_org_id NUMBER;
v_ship_to_org_id NUMBER;
v_invoice_to_org_id NUMBER;
v_ship_from_org_id NUMBER;
v_operating_unit NUMBER;
v_order_source VARCHAR2(100) := 'XELUS';
v_order_source_id NUMBER;
v_organization_code VARCHAR2(20) := 'ELG';
v_orig_sys_document_ref VARCHAR2(100);
v_orig_sys_line_ref VARCHAR2(100);
v_transactional_curr_code VARCHAR2(20);
v_price_list_id NUMBER;
v_freight_terms_code VARCHAR2(20);
v_ordered_date DATE := SYSDATE;
v_order_type VARCHAR2(100);
v_salesrep VARCHAR2(100) := 'OE INTERNAL SALESPERSON';
v_context VARCHAR2(100) := 'ADC_DOM_NT_ACCT_CST';
v_attrib_3 VARCHAR2(100) := 'Internal';
v_attrib_4 VARCHAR2(100);
v_attrib_5 VARCHAR2(50) := 'US';
v_attrib_8 VARCHAR2(100);
v_attrib_10 VARCHAR2(100) := 'XELUS';
v_customer_po_number VARCHAR2(100);
v_booked_flag VARCHAR2(100) :='Y';
v_inventory_item_id VARCHAR2(100);
v_ordered_quantity NUMBER;
v_pricing_quantity NUMBER;
v_order_quantity_uom VARCHAR2(100);
v_pricing_quantity_uom VARCHAR2(100);
v_unit_list_price NUMBER;
v_unit_selling_price NUMBER;
v_operation_code VARCHAR2(100) :='INSERT';
v_item VARCHAR2(240);
v_request_date DATE;
v_scheduled_ship_date DATE;
v_inventory_item_status_code varchar2(10);
v_customer_order_enabled_flag VARCHAR2(5);
v_hdr_err_flag VARCHAR2(5);
v_internal_err_flag VARCHAR2(5);
v_process_flag_count NUMBER;
v_hdr_exception EXCEPTION;
v_line_exception EXCEPTION;
BEGIN
-- Retrieving the Count of process_flag of table MOTC.MOTOM_SALES_ORDERS_TBL
BEGIN
SELECT COUNT(1)
INTO v_process_flag_count
FROM MOTC.MOTOM_SALES_ORDERS_TBL
WHERE process_flag = 'I';
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured at v_process_flag_count';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- HEADER LEVEL VALIDATIONS
-- Retrieving the ship_to_org_id,sold_to_org_id
IF v_process_flag_count > 0 THEN
BEGIN
SELECT hcas.cust_account_id "sold_to_org_id",
hcsu1.site_use_id "ship_to_org_id",
--hcsu1.location "ship_to_location",
hcsu2.site_use_id "invoice_to_org_id"
--hcsu2.location "invoice_to_location"
INTO v_sold_to_org_id,
v_ship_to_org_id,
v_invoice_to_org_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu1,
hz_cust_site_uses_all hcsu2
WHERE hca.account_number= 1036600692
AND hca.cust_account_id=hcas.cust_account_id
AND hcsu1.cust_acct_site_id=hcas.cust_acct_site_id
AND hcsu1.site_use_code='SHIP_TO'
AND hcsu2.cust_acct_site_id=hcas.cust_acct_site_id
AND hcsu2.site_use_code='BILL_TO';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the ship_from_org_id and operating_unit info
BEGIN
SELECT organization_id,
operating_unit
INTO v_ship_from_org_id,
v_operating_unit
FROM org_organization_definitions ood
WHERE upper(trim(organization_code))= v_organization_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the order_source_id
BEGIN
SELECT order_source_id
INTO v_order_source_id
FROM oe_order_sources
WHERE name = v_order_source;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Getting the orig_sys_document_ref
BEGIN
SELECT 'XELUS' ||' '||TO_CHAR(sysdate,'DD-MON-YY HH:MI:SS')
INTO v_orig_sys_document_ref
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Orig_Sys_Document_Ref';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
v_customer_po_number := v_orig_sys_document_ref;
-- For price_list_id
BEGIN
SELECT oota.price_list_id,
otta.freight_terms_code,
otta.currency_code,
oota.name
INTO v_price_list_id,
v_freight_terms_code,
v_transactional_curr_code,
v_order_type
FROM oe_order_types_115_all oota,
oe_transaction_types_all otta
WHERE name = v_order_type_name
AND oota.order_type_id = otta.transaction_type_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the Attribute4(Ultimate Destination Tag) info
BEGIN
SELECT address_id
INTO v_attrib_4
FROM motont_ult_dest_v
WHERE customer_number=v_customer_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg || SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the Attribute8 (Account Number)
BEGIN
SELECT code_combination_id
INTO v_attrib_8
FROM gl_code_combinations
WHERE segment1='100000'
AND segment2='140'
AND segment3='143311'
AND segment4='00000'
AND segment5='000'
AND segment6='00'
AND segment7='0000';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
--Inserting the data into oe_header_iface_all
BEGIN
INSERT INTO OE_HEADERS_IFACE_ALL (order_source_id,
orig_sys_document_ref,
org_id,
order_type,
transactional_curr_code,
sold_to_org_id,
ship_to_org_id,
invoice_to_org_id,
customer_number,
ordered_date,
freight_terms_code,
salesrep,
context,
attribute3,
attribute4,
attribute5,
attribute8,
attribute10,
customer_po_number,
booked_flag,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (v_order_source_id,
v_orig_sys_document_ref,
v_operating_unit,
v_order_type,
v_transactional_curr_code,
v_sold_to_org_id,
v_ship_to_org_id,
v_invoice_to_org_id,
v_customer_number,
v_ordered_date,
v_freight_terms_code,
v_salesrep,
v_context,
v_attrib_3,
v_attrib_4,
v_attrib_5,
v_attrib_8,
v_attrib_10,
v_customer_po_number,
v_booked_flag,
v_user_login,
SYSDATE,
v_user_login,
SYSDATE
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserted Into Headers Interface Table Successfully');
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured While Inserting Into Headers Interface Table' || SQLERRM;
RAISE v_hdr_exception;
END;
-- LINE LEVEL VALIDATIONS
FOR var_interface_cur in interface_cur
LOOP
BEGIN
v_err_msg := null;
v_orig_sys_line_ref := var_interface_cur.order_ref_number;
v_ordered_quantity := var_interface_cur.requested_qty;
v_request_date := var_interface_cur.due_date;
v_scheduled_ship_date:= var_interface_cur.ship_date;
v_pricing_quantity := v_ordered_quantity;
-- RETRIEVING INVENTORY_ITEM_ID,item,customer_order_enable_flag,inventory_item_status_code data.
BEGIN
SELECT inventory_item_id,
list_price_per_unit,
primary_uom_code,
customer_order_enabled_flag,
inventory_item_status_code,
segment1
INTO v_inventory_item_id,
v_unit_list_price,
v_pricing_quantity_uom,
v_customer_order_enabled_flag,
v_inventory_item_status_code,
v_item
FROM mtl_system_items_b
WHERE segment1 = var_interface_cur.partnum_ordered
AND organization_id = v_ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_internal_err_flag := 'Y';
v_err_msg := 'No Data Found For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_line_exception;
WHEN TOO_MANY_ROWS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Too Many Values Returned For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_line_exception;
WHEN OTHERS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Exception Occured For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_line_exception;
END;
v_unit_selling_price := v_unit_list_price;
v_order_quantity_uom := v_pricing_quantity_uom;
-- Inserting data into OE_LINES_IFACE_ALL
IF v_customer_order_enabled_flag = 'Y' THEN
IF v_inventory_item_status_code <> '05' THEN
BEGIN
INSERT INTO OE_LINES_IFACE_ALL (orig_sys_document_ref,
orig_sys_line_ref,
org_id,
ship_from_org_id,
inventory_item_id,
ordered_quantity,
pricing_quantity,
order_quantity_uom,
unit_list_price,
unit_selling_price,
pricing_quantity_uom,
price_list_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
order_source_id,
operation_code,
request_date,
schedule_ship_date
)
VALUES (v_orig_sys_document_ref,
v_orig_sys_line_ref,
v_operating_unit,
v_ship_from_org_id,
v_inventory_item_id,
v_ordered_quantity,
v_pricing_quantity,
v_order_quantity_uom,
v_unit_list_price,
v_unit_selling_price,
v_pricing_quantity_uom,
v_price_list_id,
v_user_login,
SYSDATE,
v_user_login,
SYSDATE,
v_order_source_id,
v_operation_code,
v_request_date,
v_scheduled_ship_date
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insertion Into Lines Interface Table Successfully');
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'P'
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated MOTC.MOTOM_SALES_ORDERS_TBL Table With Process Flag as ''P'' For Order Ref Number'||v_orig_sys_line_ref);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Exception Occured While Inserting Into Headers Interface Table' || SQLERRM;
RAISE v_line_exception;
END;
ELSE
v_internal_err_flag := 'Y';
v_err_msg := 'Item is in Absolute status in oracle System';
RAISE v_line_exception;
END IF;
ELSE
v_internal_err_flag := 'Y';
v_err_msg := 'Customer Order Enabled Flag Not Enabled';
RAISE v_line_exception;
END IF;
EXCEPTION
WHEN v_line_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag ='E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Is Updated With Process_Flag ''E'' For Order Ref Number '||v_orig_sys_line_ref ||' Error Msg -'|| v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag ='E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Is Updated With Process_Flag ''E'' For Order Ref Number '||v_orig_sys_line_ref ||' Error Msg -'|| v_err_msg);
END;
END LOOP;
ELSE
v_err_msg := 'No Header details inserted';
RAISE v_hdr_exception;
END IF;
EXCEPTION
WHEN v_hdr_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
set process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E'' At Header Level'||v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
set process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E''At Header Level'||v_err_msg);
END validation_insertion_proc;
/* This procedure is for validating whether customer and order type existed or not and calling the
validation_insertion_proc procedure */
PROCEDURE main_validation_proc(errbuf OUT VARCHAR2,retcode OUT NUMBER)
AS
v_main_exception EXCEPTION;
BEGIN
BEGIN
SELECT customer_number
INTO v_customer_number
FROM RA_CUSTOMERS
WHERE upper(customer_name) = 'MOTOROLA EL PASO JUAREZ SERVICE CENTER';
error_flag := 'N';
EXCEPTION
WHEN NO_DATA_FOUND THEN
error_flag := 'Y';
v_err_msg := 'Customer (MOTOROLA EL PASO JUAREZ SERVICE CENTER) Does Not Exist';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg||SQLERRM);
RAISE v_main_exception;
WHEN TOO_MANY_ROWS THEN
error_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Customer Number';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || ''||SQLERRM);
RAISE v_main_exception;
WHEN OTHERS THEN
error_flag := 'Y';
v_err_msg := 'Exception Occured For Customer Number';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg||''||SQLERRM);
RAISE v_main_exception;
END;
BEGIN
SELECT name
INTO v_order_type_name
FROM oe_order_types_115_all
WHERE upper(name) = 'ADC_DOM_NT_ACCT_CST';
error_flag := 'N';
dbms_output.put_line('error_order_type');
EXCEPTION
WHEN NO_DATA_FOUND THEN
error_flag := 'Y';
v_err_msg := 'Order Type (ADC_DOM_NT_ACCT_CST) Does not Exist';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
WHEN TOO_MANY_ROWS THEN
error_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Order Type (ADC_DOM_NT_ACCT_CST)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
WHEN OTHERS THEN
error_flag := 'Y';
v_err_msg := 'Exception Occured For Order Type (ADC_DOM_NT_ACCT_CST)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
END;
-- calling validation_insertion_proc to validate header and line level details.
validation_insertion_proc;
EXCEPTION
WHEN v_main_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag As ''E'' At Main Validation Level -'||v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E'' At Main Validation Level -'||v_err_msg);
END main_validation_proc;
END mot_j2j_sales_order_conv_pkg;
/
========================end of package body========================
============================order import mail sending=================
#!/bin/ksh
#-----------------------------------------------------------------------------#
# Program Name : Order Import Mail Notification
#
# Program Type : Unix shell script
#
# Module Name : Order Import Interface
#
# Description : Program will send a notification with order number to users
# once the order import successfully completed
# Change History :
# Who When Description Version No.
#---------------------------------------------------------------------------------
# KNW438 28-Jul-2011 Initial Creation 1.0
#---------------------------------------------------------------------------------
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
# Connecting to DB to update staging table with status as 'C'
sqlplus -s $login_usr_pwd<<EOF
set heading off
UPDATE motc.motom_sales_orders_tbl
SET process_flag='C'
,last_updated_by = $conc_user_id
,last_update_date = sysdate
WHERE process_flag='P'
AND email_sent = 'N'
AND order_ref_number IN (
SELECT oola.orig_sys_line_ref
FROM
oe_order_lines_all oola
,oe_order_headers_all ooha
WHERE
ooha.header_id=oola.header_id
AND ooha.orig_sys_document_ref LIKE 'XELUS%'
AND ooha.creation_date>=TRUNC(SYSDATE));
COMMIT;
EXIT
EOF
# Connecting to DB to get the imported order number
touch $MOTONT_TOP/bin/sqllog
chmod 777 $MOTONT_TOP/bin/sqllog
sqlplus -s $login_usr_pwd<<EOF > $MOTONT_TOP/bin/sqllog
set heading off
SELECT ooha.order_number
FROM oe_order_headers_all ooha,
oe_order_lines_all oel,
motc.motom_sales_orders_tbl sot
WHERE ooha.orig_sys_document_ref LIKE 'XELUS%'
AND oel.header_id = ooha.header_id
AND oel.orig_sys_line_ref = sot.order_ref_number
AND sot.email_sent = 'N'
AND ooha.creation_date>=TRUNC(SYSDATE)
order by order_number desc;
EXIT
EOF
if [ -s $MOTONT_TOP/bin/sqllog ]
then
grep "no rows selected" $MOTONT_TOP/bin/sqllog
if [ $? = 0 ]
then
echo "No Order Created"
else
order_number=`awk -F" " 'NR==2{print $1}' $MOTONT_TOP/bin/sqllog`
echo 'Order Number Imported: '$order_number
# Sending mail notification to users with Imported Order Number
echo 'Order Number: '$order_number | mailx -s 'Order Has Been Created For XELUS System' NA11IXEL@motorolasolutions.com
echo 'Mail Notification Successfully Sent'
# Connecting to DB to update staging table with status as 'C'
sqlplus -s $login_usr_pwd<<EOF
set heading off
UPDATE motc.motom_sales_orders_tbl
SET email_sent='Y'
,last_updated_by = $conc_user_id
,last_update_date = sysdate
WHERE process_flag='C'
AND email_sent = 'N'
AND order_ref_number IN (
SELECT oola.orig_sys_line_ref
FROM
oe_order_lines_all oola
,oe_order_headers_all ooha
WHERE
ooha.header_id=oola.header_id
AND ooha.orig_sys_document_ref LIKE 'XELUS%'
AND ooha.creation_date>=TRUNC(SYSDATE));
COMMIT;
EXIT
EOF
fi
rm $MOTONT_TOP/bin/sqllog
fi
===============================end of order import mail sending===========
===
#---------------------------------------------------------------------------------
# Application : Motorola Order Management
# Program : MOTONT_SALES_ORDER_SH
# Author : tcs
# Date : 28-Jul-2011
# Type of program : Shell
# Description: : Program to Load Sales Order details to Staging table.
# Change History :
# Who When Description Version No.
#---------------------------------------------------------------------------------
# KNW438 28-Jul-2011 Initial Creation 1.0
#---------------------------------------------------------------------------------
# Local variable definitions
LV_REQ_ID=`echo $*|awk '{print $2}'|cut -f2 -d'='`
LV_UIDPWD=`echo $*|awk '{print $3}'|cut -f2 -d'"'`
LV_USER_ID=`echo $*|awk '{print $4}'|cut -f2 -d'='`
LV_FILE_NAME=`echo $*|awk '{print $9}'|cut -f2 -d'"'`
LV_DATA_PATH=`echo $*|awk '{print $10}'|cut -f2 -d'"'`
LV_BAD_PATH=`echo $*|awk '{print $11}'|cut -f2 -d'"'`
LV_Log_PATH=`echo $*|awk '{print $12}'|cut -f2 -d'"'`
TSTAMP=`date '+%Y%m%d%H%M%S'`
echo $LV_DATA_PATH
echo 'data_top: '$DATA_TOP
control_file()
{
LV_CTL_DATA="LOAD DATA
APPEND
INTO TABLE MOTC.MOTOM_SALES_ORDERS_TBL
WHEN (52) = 'A'
TRAILING NULLCOLS
(Record_type POSITION(1:4),
Partnum_ordered POSITION(5:23),
Source_loc POSITION(24:37),
customer_loc POSITION(38:51),
order_type POSITION(52:52),
order_ref_number POSITION(53:72),
due_date POSITION(73:80) date(8) 'YYYYMMDD',
ship_date POSITION(81:88) date(8) 'YYYYMMDD',
confirm_qty POSITION(89:96),
requested_qty POSITION(97:104),
purchase_order_num POSITION(105:124),
reforder_ref_num POSITION(125:144),
mode_of_transport POSITION(145:146),
order_outbound POSITION(147:226),
create_date POSITION(227:234) date(8) 'YYYYMMDD',
source_price POSITION(235:243),
src_price_curr_code POSITION(244:251),
extend_src_price POSITION(252:260),
unyield_order_qty POSITION(261:268),
yield_order_qty POSITION(269:276),
good_asit_gets_flag POSITION(277:277),
endoflife_order_flag POSITION(278:278),
repair_from_part POSITION(279:297),
limited_use_order_qty POSITION(298:305),
yield_confirm_qty POSITION(306:313),
unyield_confirm_qty POSITION(314:321),
creation_date SYSDATE,
process_flag CONSTANT 'I',
last_update_date SYSDATE,
email_sent CONSTANT 'N')"
echo $LV_CTL_DATA > "$MOTFND_TOP/bin/MOTINV_SALES_ORDERS.ctl"
}
control_file
echo "File Name:"$LV_FILE_NAME
if [ -f $LV_DATA_PATH/$LV_FILE_NAME ]
then
if [ -s $LV_DATA_PATH/$LV_FILE_NAME ] #Check for Blank files
then
echo "Starting SQL*Loader"
sqlldr userid=$LV_UIDPWD control=$MOTFND_TOP/bin/MOTINV_SALES_ORDERS.ctl data=$LV_DATA_PATH/$LV_FILE_NAME log=$LV_Log_PATH/$LV_REQ_ID.log bad=$LV_BAD_PATH/$LV_REQ_ID.bad
if [ $? = 0 ] #Check for Sqlloader Execution
then
echo "sqlloader execution completed successfully. \n"
else
echo "sqlloader execution completed with errors. \n"
fi
else
echo "${LV_FILE_NAME} does not contain any data. "
echo "Subject: XELUS Sales Order file does not contain any data" > $MOTINV_TOP/bin/salesorderdata.body
echo "Hi" >> $MOTINV_TOP/bin/salesorderdata.body
echo " " >> $MOTINV_TOP/bin/salesorderdata.body
echo "No Xelus Orders were sent." >> $MOTINV_TOP/bin/salesorderdata.body
echo " " >> $MOTINV_TOP/bin/salesorderdata.body
echo "Thank you," >> $MOTINV_TOP/bin//salesorderdata.body
chmod 755 $MOTINV_TOP/bin/salesorderdata.body
(cat $MOTINV_TOP/bin/salesorderdata.body;)|mail NA11IXEL@motorolasolutions.com
fi
else
echo "${LV_FILE_NAME} file does not exists. "
echo "Subject: XELUS Sales Order file does not exists" > $MOTINV_TOP/bin/salesorderfile.body
echo "Hi" >> $MOTINV_TOP/bin/salesorderfile.body
echo " " >> $MOTINV_TOP/bin/salesorderfile.body
echo "No Xelus Orders were sent." >> $MOTINV_TOP/bin/salesorderfile.body
echo " " >> $MOTINV_TOP/bin/salesorderfile.body
echo "Thank you," >> $MOTINV_TOP/bin//salesorderfile.body
chmod 755 $MOTINV_TOP/bin/salesorderfile.body
(cat $MOTINV_TOP/bin/salesorderfile.body;)|mail NA11IXEL@motorolasolutions.com
fi
echo "**************************************************************************************"
# Removing the data file after loading
rm $LV_DATA_PATH/$LV_FILE_NAME
if [ -s $LV_BAD_PATH/$LV_REQ_ID.bad ]
then
echo "Subject: XELUS Sales Order bad records" > $MOTINV_TOP/bin/salesordermail.body
echo "Hi" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/salesordermail.body
echo "The error list of XELUS Sales order records are attached with this mail" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/salesordermail.body
echo "Please find the attachments" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/mail.body
echo "Thank you" >> $MOTINV_TOP/bin/salesordermail.body
chmod 755 $MOTINV_TOP/bin/salesordermail.body
(cat $MOTINV_TOP/bin/salesordermail.body; uuencode $LV_BAD_PATH/$LV_REQ_ID.bad XELUS_Sales_Order_${LV_REQ_ID}.bad)|mail NA11IXEL@motorolasolutions.com
badcontrol_file()
{
LV_BAD_CTL_DATA="LOAD DATA
APPEND
INTO TABLE MOTC.MOTOM_SALES_ORDERS_BAD_TBL
TRAILING NULLCOLS
(Record_type POSITION(1:4),
Partnum_ordered POSITION(5:23),
Source_loc POSITION(24:37),
customer_loc POSITION(38:51),
order_type POSITION(52:52),
order_ref_number POSITION(53:72),
due_date POSITION(73:80) date(8) 'YYYYMMDD',
ship_date POSITION(81:88) date(8) 'YYYYMMDD',
confirm_qty POSITION(89:96),
requested_qty POSITION(97:104),
purchase_order_num POSITION(105:124),
reforder_ref_num POSITION(125:144),
mode_of_transport POSITION(145:146),
order_outbound POSITION(147:226),
create_date POSITION(227:234) date(8) 'YYYYMMDD',
source_price POSITION(235:243),
src_price_curr_code POSITION(244:251),
extend_src_price POSITION(252:260),
unyield_order_qty POSITION(261:268),
yield_order_qty POSITION(269:276),
good_asit_gets_flag POSITION(277:277),
endoflife_order_flag POSITION(278:278),
repair_from_part POSITION(279:297),
limited_use_order_qty POSITION(298:305),
yield_confirm_qty POSITION(306:313),
unyield_confirm_qty POSITION(314:321),
creation_date SYSDATE,
last_update_date SYSDATE,
email_sent CONSTANT 'N'
)"
echo $LV_BAD_CTL_DATA > "$MOTFND_TOP/bin/MOTINV_SALES_ORDERS_BAD.ctl"
}
badcontrol_file
echo "File Name:"$LV_BAD_PATH/$LV_REQ_ID.bad
echo "Starting SQL*Loader"
sqlldr userid=$LV_UIDPWD control=$MOTFND_TOP/bin/MOTINV_SALES_ORDERS_BAD.ctl data=$LV_BAD_PATH/$LV_REQ_ID.bad log=$LV_Log_PATH/$LV_REQ_ID.log
if [ $? = 0 ] #Check for Sqlloader Execution
then
echo "sqlloader execution completed successfully. \n"
else
echo "sqlloader execution completed with errors. \n"
fi
else
echo "${LV_FILE_NAME} does not contain any Bad data. "
fi
rm $LV_BAD_PATH/$LV_REQ_ID.bad
echo " \n "
echo " \n "
================================end of header==
==============bad table=======================
---------------------------------------------------------------------------------
-- Application : Motorola Order Management
-- Program : MOTOM_SALES_ORDERS_BAD_TBL
-- Author : tcs
-- Date : 28-Jul-2011
-- Type of program : SQl Program
-- Description: : Staging table for Sales Order Bad records.
--Change History :
-- Who When Description Version No.
--------------------------------------------------------------------------------------------
-- KNW438 28-JUL-2011 Initial Creation 1.0
--------------------------------------------------------------------------------------------
CREATE TABLE MOTC.MOTOM_SALES_ORDERS_BAD_TBL
(
RECORD_TYPE VARCHAR2(4 BYTE),
PARTNUM_ORDERED VARCHAR2(19 BYTE),
SOURCE_LOC VARCHAR2(70 BYTE),
CUSTOMER_LOC VARCHAR2(14 BYTE),
ORDER_TYPE VARCHAR2(1 BYTE),
ORDER_REF_NUMBER VARCHAR2(20 BYTE),
DUE_DATE VARCHAR2(20 BYTE),
SHIP_DATE VARCHAR2(20 BYTE),
CONFIRM_QTY VARCHAR2(20 BYTE),
REQUESTED_QTY VARCHAR2(20 BYTE),
PURCHASE_ORDER_NUM VARCHAR2(20 BYTE),
REFORDER_REF_NUM VARCHAR2(20 BYTE),
MODE_OF_TRANSPORT VARCHAR2(2 BYTE),
ORDER_OUTBOUND VARCHAR2(90 BYTE),
CREATE_DATE VARCHAR2(20 BYTE),
SOURCE_PRICE VARCHAR2(9 BYTE),
SRC_PRICE_CURR_CODE VARCHAR2(8 BYTE),
EXTEND_SRC_PRICE VARCHAR2(20 BYTE),
UNYIELD_ORDER_QTY VARCHAR2(20 BYTE),
YIELD_ORDER_QTY VARCHAR2(20 BYTE),
GOOD_ASIT_GETS_FLAG VARCHAR2(1 BYTE),
ENDOFLIFE_ORDER_FLAG VARCHAR2(1 BYTE),
REPAIR_FROM_PART VARCHAR2(19 BYTE),
LIMITED_USE_ORDER_QTY VARCHAR2(20 BYTE),
YIELD_CONFIRM_QTY VARCHAR2(20 BYTE),
UNYIELD_CONFIRM_QTY VARCHAR2(20 BYTE),
CREATED_BY VARCHAR2(20 BYTE),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(20 BYTE),
LAST_UPDATE_DATE DATE,
PROCESS_FLAG VARCHAR2(1 BYTE),
ERR_MSG VARCHAR2(200 BYTE),
EMAIL_SENT VARCHAR2(10 BYTE)
)
TABLESPACE MOTC
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE SYNONYM APPS.MOTOM_SALES_ORDERS_BAD_TBL FOR MOTC.MOTOM_SALES_ORDERS_BAD_TBL;
===========================sales order table===================
---------------------------------------------------------------------------------
-- Application : Motorola Order Management
-- Program : MOTOM_SALES_ORDERS_TBL
-- Author : tcs
-- Date : 28-Jul-2011
-- Type of program : SQl Program
-- Description: : Staging table for Sales Order Program.
--Change History :
-- Who When Description Version No.
----------------------------------------------------------------------------------------------
-- KNW438 28-JUL-2011 Initial Creation 1.0
-----------------------------------------------------------------------------------------------
CREATE TABLE MOTC.MOTOM_SALES_ORDERS_TBL
(
RECORD_TYPE VARCHAR2(4 BYTE),
PARTNUM_ORDERED VARCHAR2(19 BYTE),
SOURCE_LOC VARCHAR2(70 BYTE),
CUSTOMER_LOC VARCHAR2(14 BYTE),
ORDER_TYPE VARCHAR2(1 BYTE),
ORDER_REF_NUMBER VARCHAR2(20 BYTE),
DUE_DATE DATE,
SHIP_DATE DATE,
CONFIRM_QTY NUMBER(8),
REQUESTED_QTY NUMBER(8),
PURCHASE_ORDER_NUM NUMBER(20),
REFORDER_REF_NUM NUMBER(20),
MODE_OF_TRANSPORT VARCHAR2(2 BYTE),
ORDER_OUTBOUND VARCHAR2(90 BYTE),
CREATE_DATE DATE,
SOURCE_PRICE VARCHAR2(9 BYTE),
SRC_PRICE_CURR_CODE VARCHAR2(8 BYTE),
EXTEND_SRC_PRICE NUMBER(6,3),
UNYIELD_ORDER_QTY NUMBER(8),
YIELD_ORDER_QTY NUMBER(8),
GOOD_ASIT_GETS_FLAG VARCHAR2(1 BYTE),
ENDOFLIFE_ORDER_FLAG VARCHAR2(1 BYTE),
REPAIR_FROM_PART VARCHAR2(19 BYTE),
LIMITED_USE_ORDER_QTY NUMBER(8),
YIELD_CONFIRM_QTY NUMBER(8),
UNYIELD_CONFIRM_QTY NUMBER(8),
CREATED_BY NUMBER,
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
PROCESS_FLAG VARCHAR2(1 BYTE),
ERR_MSG VARCHAR2(200 BYTE),
EMAIL_SENT VARCHAR2(10 BYTE)
)
TABLESPACE MOTC
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE SYNONYM APPS.MOTOM_SALES_ORDERS_TBL FOR MOTC.MOTOM_SALES_ORDERS_TBL;
===============================
AS
--/****************************************************************************************************/
--/* */
--/* Header : mot_j2j_sales_order_conv_pkg.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : This package is used to create Sales Orders */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTOM_SALES_ORDERS_TBL, OC.MOTOM_SALES_ORDERS_BAD_TBL */
--/* API's/Open Interfaces: */
--/* OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL */
--/* */
--/* Value Sets: */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* Modification History : */
--/* 28-Jul-2011 v0.1 Initial Creation */
--/* */
--/****************************************************************************************************/
CURSOR interface_cur
IS
SELECT *
FROM MOTC.MOTOM_SALES_ORDERS_TBL
WHERE process_flag = 'I'
OR process_flag IS NULL;
error_flag VARCHAR2(50);
v_err_buf VARCHAR2(50);
v_ret_code NUMBER;
v_customer_number NUMBER;
v_order_type_name VARCHAR2(100);
v_process_flag VARCHAR2(100);
v_hdr_err_flag VARCHAR2(50) := 'N';
v_err_msg VARCHAR2(1000):= NULL;
v_user_login VARCHAR2(100) := FND_PROFILE.VALUE('USER_ID');
PROCEDURE validation_insertion_proc;
PROCEDURE main_validation_proc(errbuf OUT VARCHAR2,retcode OUT NUMBER);
END mot_j2j_sales_order_conv_pkg;
/
========================================================package body=================================
--/****************************************************************************************************/
--/* */
--/* Header : mot_j2j_sales_order_conv_pkg.sql */
--/* */
--/* Program Type: PL/SQL Package */
--/* */
--/* Creation : 28-Jul-2011 */
--/* */
--/* Author : TCS */
--/* */
--/* Description : Validating whether Customer and Order Type exist or not */
--/* and based on that inserting Data into Order Interface Tables from Staging Table */
--/* by calling two procedures main_validation_proc,validation_insertion_proc. */
--/* */
--/* DEPENDENCIES : The Following Table and Procedures are the Dependencies for this package */
--/* Table Name: */
--/* MOTC.MOTOM_SALES_ORDERS_TBL, OC.MOTOM_SALES_ORDERS_BAD_TBL */
--/* API's/Open Interfaces: */
--/* OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL */
--/* */
--/* Value Sets: */
--/* None */
--/* */
--/* Caller : Concurrent Program */
--/* */
--/* Naming Conv : P_ - Parameter variables */
--/* V_ - Global variables */
--/* L_ - local variables */
--/* */
--/* Modification History : */
--/* 28-Jul-2011 v0.1 Initial Creation */
--/* */
--/****************************************************************************************************/
CREATE OR REPLACE PACKAGE BODY mot_j2j_sales_order_conv_pkg
AS
/* This Procedure is doing validation at header level as well as line level and then inserting
data into interface tables */
PROCEDURE validation_insertion_proc
AS
v_sold_to_org_id NUMBER;
v_ship_to_org_id NUMBER;
v_invoice_to_org_id NUMBER;
v_ship_from_org_id NUMBER;
v_operating_unit NUMBER;
v_order_source VARCHAR2(100) := 'XELUS';
v_order_source_id NUMBER;
v_organization_code VARCHAR2(20) := 'ELG';
v_orig_sys_document_ref VARCHAR2(100);
v_orig_sys_line_ref VARCHAR2(100);
v_transactional_curr_code VARCHAR2(20);
v_price_list_id NUMBER;
v_freight_terms_code VARCHAR2(20);
v_ordered_date DATE := SYSDATE;
v_order_type VARCHAR2(100);
v_salesrep VARCHAR2(100) := 'OE INTERNAL SALESPERSON';
v_context VARCHAR2(100) := 'ADC_DOM_NT_ACCT_CST';
v_attrib_3 VARCHAR2(100) := 'Internal';
v_attrib_4 VARCHAR2(100);
v_attrib_5 VARCHAR2(50) := 'US';
v_attrib_8 VARCHAR2(100);
v_attrib_10 VARCHAR2(100) := 'XELUS';
v_customer_po_number VARCHAR2(100);
v_booked_flag VARCHAR2(100) :='Y';
v_inventory_item_id VARCHAR2(100);
v_ordered_quantity NUMBER;
v_pricing_quantity NUMBER;
v_order_quantity_uom VARCHAR2(100);
v_pricing_quantity_uom VARCHAR2(100);
v_unit_list_price NUMBER;
v_unit_selling_price NUMBER;
v_operation_code VARCHAR2(100) :='INSERT';
v_item VARCHAR2(240);
v_request_date DATE;
v_scheduled_ship_date DATE;
v_inventory_item_status_code varchar2(10);
v_customer_order_enabled_flag VARCHAR2(5);
v_hdr_err_flag VARCHAR2(5);
v_internal_err_flag VARCHAR2(5);
v_process_flag_count NUMBER;
v_hdr_exception EXCEPTION;
v_line_exception EXCEPTION;
BEGIN
-- Retrieving the Count of process_flag of table MOTC.MOTOM_SALES_ORDERS_TBL
BEGIN
SELECT COUNT(1)
INTO v_process_flag_count
FROM MOTC.MOTOM_SALES_ORDERS_TBL
WHERE process_flag = 'I';
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured at v_process_flag_count';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- HEADER LEVEL VALIDATIONS
-- Retrieving the ship_to_org_id,sold_to_org_id
IF v_process_flag_count > 0 THEN
BEGIN
SELECT hcas.cust_account_id "sold_to_org_id",
hcsu1.site_use_id "ship_to_org_id",
--hcsu1.location "ship_to_location",
hcsu2.site_use_id "invoice_to_org_id"
--hcsu2.location "invoice_to_location"
INTO v_sold_to_org_id,
v_ship_to_org_id,
v_invoice_to_org_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu1,
hz_cust_site_uses_all hcsu2
WHERE hca.account_number= 1036600692
AND hca.cust_account_id=hcas.cust_account_id
AND hcsu1.cust_acct_site_id=hcas.cust_acct_site_id
AND hcsu1.site_use_code='SHIP_TO'
AND hcsu2.cust_acct_site_id=hcas.cust_acct_site_id
AND hcsu2.site_use_code='BILL_TO';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Ship_To Org_Id And Sold_To_Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the ship_from_org_id and operating_unit info
BEGIN
SELECT organization_id,
operating_unit
INTO v_ship_from_org_id,
v_operating_unit
FROM org_organization_definitions ood
WHERE upper(trim(organization_code))= v_organization_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Ship_From Org_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the order_source_id
BEGIN
SELECT order_source_id
INTO v_order_source_id
FROM oe_order_sources
WHERE name = v_order_source;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Order_Source_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Getting the orig_sys_document_ref
BEGIN
SELECT 'XELUS' ||' '||TO_CHAR(sysdate,'DD-MON-YY HH:MI:SS')
INTO v_orig_sys_document_ref
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Orig_Sys_Document_Ref';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
v_customer_po_number := v_orig_sys_document_ref;
-- For price_list_id
BEGIN
SELECT oota.price_list_id,
otta.freight_terms_code,
otta.currency_code,
oota.name
INTO v_price_list_id,
v_freight_terms_code,
v_transactional_curr_code,
v_order_type
FROM oe_order_types_115_all oota,
oe_transaction_types_all otta
WHERE name = v_order_type_name
AND oota.order_type_id = otta.transaction_type_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Price_List_Id';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the Attribute4(Ultimate Destination Tag) info
BEGIN
SELECT address_id
INTO v_attrib_4
FROM motont_ult_dest_v
WHERE customer_number=v_customer_number;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG, v_err_msg || SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Attribute4 (Ultimate Destination Tag)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_hdr_exception;
END;
-- Retrieving the Attribute8 (Account Number)
BEGIN
SELECT code_combination_id
INTO v_attrib_8
FROM gl_code_combinations
WHERE segment1='100000'
AND segment2='140'
AND segment3='143311'
AND segment4='00000'
AND segment5='000'
AND segment6='00'
AND segment7='0000';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'No Data Found For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN TOO_MANY_ROWS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured For Attribute8 (Account Number)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_hdr_exception;
END;
--Inserting the data into oe_header_iface_all
BEGIN
INSERT INTO OE_HEADERS_IFACE_ALL (order_source_id,
orig_sys_document_ref,
org_id,
order_type,
transactional_curr_code,
sold_to_org_id,
ship_to_org_id,
invoice_to_org_id,
customer_number,
ordered_date,
freight_terms_code,
salesrep,
context,
attribute3,
attribute4,
attribute5,
attribute8,
attribute10,
customer_po_number,
booked_flag,
created_by,
creation_date,
last_updated_by,
last_update_date)
VALUES (v_order_source_id,
v_orig_sys_document_ref,
v_operating_unit,
v_order_type,
v_transactional_curr_code,
v_sold_to_org_id,
v_ship_to_org_id,
v_invoice_to_org_id,
v_customer_number,
v_ordered_date,
v_freight_terms_code,
v_salesrep,
v_context,
v_attrib_3,
v_attrib_4,
v_attrib_5,
v_attrib_8,
v_attrib_10,
v_customer_po_number,
v_booked_flag,
v_user_login,
SYSDATE,
v_user_login,
SYSDATE
);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserted Into Headers Interface Table Successfully');
EXCEPTION
WHEN OTHERS THEN
v_hdr_err_flag := 'Y';
v_err_msg := 'Exception Occured While Inserting Into Headers Interface Table' || SQLERRM;
RAISE v_hdr_exception;
END;
-- LINE LEVEL VALIDATIONS
FOR var_interface_cur in interface_cur
LOOP
BEGIN
v_err_msg := null;
v_orig_sys_line_ref := var_interface_cur.order_ref_number;
v_ordered_quantity := var_interface_cur.requested_qty;
v_request_date := var_interface_cur.due_date;
v_scheduled_ship_date:= var_interface_cur.ship_date;
v_pricing_quantity := v_ordered_quantity;
-- RETRIEVING INVENTORY_ITEM_ID,item,customer_order_enable_flag,inventory_item_status_code data.
BEGIN
SELECT inventory_item_id,
list_price_per_unit,
primary_uom_code,
customer_order_enabled_flag,
inventory_item_status_code,
segment1
INTO v_inventory_item_id,
v_unit_list_price,
v_pricing_quantity_uom,
v_customer_order_enabled_flag,
v_inventory_item_status_code,
v_item
FROM mtl_system_items_b
WHERE segment1 = var_interface_cur.partnum_ordered
AND organization_id = v_ship_from_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_internal_err_flag := 'Y';
v_err_msg := 'No Data Found For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_line_exception;
WHEN TOO_MANY_ROWS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Too Many Values Returned For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_line_exception;
WHEN OTHERS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Exception Occured For Inventory Item';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || SQLERRM);
RAISE v_line_exception;
END;
v_unit_selling_price := v_unit_list_price;
v_order_quantity_uom := v_pricing_quantity_uom;
-- Inserting data into OE_LINES_IFACE_ALL
IF v_customer_order_enabled_flag = 'Y' THEN
IF v_inventory_item_status_code <> '05' THEN
BEGIN
INSERT INTO OE_LINES_IFACE_ALL (orig_sys_document_ref,
orig_sys_line_ref,
org_id,
ship_from_org_id,
inventory_item_id,
ordered_quantity,
pricing_quantity,
order_quantity_uom,
unit_list_price,
unit_selling_price,
pricing_quantity_uom,
price_list_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
order_source_id,
operation_code,
request_date,
schedule_ship_date
)
VALUES (v_orig_sys_document_ref,
v_orig_sys_line_ref,
v_operating_unit,
v_ship_from_org_id,
v_inventory_item_id,
v_ordered_quantity,
v_pricing_quantity,
v_order_quantity_uom,
v_unit_list_price,
v_unit_selling_price,
v_pricing_quantity_uom,
v_price_list_id,
v_user_login,
SYSDATE,
v_user_login,
SYSDATE,
v_order_source_id,
v_operation_code,
v_request_date,
v_scheduled_ship_date
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insertion Into Lines Interface Table Successfully');
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'P'
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Updated MOTC.MOTOM_SALES_ORDERS_TBL Table With Process Flag as ''P'' For Order Ref Number'||v_orig_sys_line_ref);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_internal_err_flag := 'Y';
v_err_msg := 'Exception Occured While Inserting Into Headers Interface Table' || SQLERRM;
RAISE v_line_exception;
END;
ELSE
v_internal_err_flag := 'Y';
v_err_msg := 'Item is in Absolute status in oracle System';
RAISE v_line_exception;
END IF;
ELSE
v_internal_err_flag := 'Y';
v_err_msg := 'Customer Order Enabled Flag Not Enabled';
RAISE v_line_exception;
END IF;
EXCEPTION
WHEN v_line_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag ='E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Is Updated With Process_Flag ''E'' For Order Ref Number '||v_orig_sys_line_ref ||' Error Msg -'|| v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag ='E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE order_ref_number = v_orig_sys_line_ref;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Is Updated With Process_Flag ''E'' For Order Ref Number '||v_orig_sys_line_ref ||' Error Msg -'|| v_err_msg);
END;
END LOOP;
ELSE
v_err_msg := 'No Header details inserted';
RAISE v_hdr_exception;
END IF;
EXCEPTION
WHEN v_hdr_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
set process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E'' At Header Level'||v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
set process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E''At Header Level'||v_err_msg);
END validation_insertion_proc;
/* This procedure is for validating whether customer and order type existed or not and calling the
validation_insertion_proc procedure */
PROCEDURE main_validation_proc(errbuf OUT VARCHAR2,retcode OUT NUMBER)
AS
v_main_exception EXCEPTION;
BEGIN
BEGIN
SELECT customer_number
INTO v_customer_number
FROM RA_CUSTOMERS
WHERE upper(customer_name) = 'MOTOROLA EL PASO JUAREZ SERVICE CENTER';
error_flag := 'N';
EXCEPTION
WHEN NO_DATA_FOUND THEN
error_flag := 'Y';
v_err_msg := 'Customer (MOTOROLA EL PASO JUAREZ SERVICE CENTER) Does Not Exist';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg||SQLERRM);
RAISE v_main_exception;
WHEN TOO_MANY_ROWS THEN
error_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Customer Number';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg || ''||SQLERRM);
RAISE v_main_exception;
WHEN OTHERS THEN
error_flag := 'Y';
v_err_msg := 'Exception Occured For Customer Number';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg||''||SQLERRM);
RAISE v_main_exception;
END;
BEGIN
SELECT name
INTO v_order_type_name
FROM oe_order_types_115_all
WHERE upper(name) = 'ADC_DOM_NT_ACCT_CST';
error_flag := 'N';
dbms_output.put_line('error_order_type');
EXCEPTION
WHEN NO_DATA_FOUND THEN
error_flag := 'Y';
v_err_msg := 'Order Type (ADC_DOM_NT_ACCT_CST) Does not Exist';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
WHEN TOO_MANY_ROWS THEN
error_flag := 'Y';
v_err_msg := 'Too Many Rows Returned For Order Type (ADC_DOM_NT_ACCT_CST)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
WHEN OTHERS THEN
error_flag := 'Y';
v_err_msg := 'Exception Occured For Order Type (ADC_DOM_NT_ACCT_CST)';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_err_msg ||SQLERRM);
RAISE v_main_exception;
END;
-- calling validation_insertion_proc to validate header and line level details.
validation_insertion_proc;
EXCEPTION
WHEN v_main_exception THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag As ''E'' At Main Validation Level -'||v_err_msg);
WHEN OTHERS THEN
UPDATE MOTC.MOTOM_SALES_ORDERS_TBL
SET process_flag = 'E'
,err_msg = v_err_msg
,last_update_date = SYSDATE
,last_updated_by = v_user_login
WHERE process_flag = 'I';
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'MOTC.MOTOM_SALES_ORDERS_TBL Table Updated With Process Flag as ''E'' At Main Validation Level -'||v_err_msg);
END main_validation_proc;
END mot_j2j_sales_order_conv_pkg;
/
========================end of package body========================
============================order import mail sending=================
#!/bin/ksh
#-----------------------------------------------------------------------------#
# Program Name : Order Import Mail Notification
#
# Program Type : Unix shell script
#
# Module Name : Order Import Interface
#
# Description : Program will send a notification with order number to users
# once the order import successfully completed
# Change History :
# Who When Description Version No.
#---------------------------------------------------------------------------------
# KNW438 28-Jul-2011 Initial Creation 1.0
#---------------------------------------------------------------------------------
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
# Connecting to DB to update staging table with status as 'C'
sqlplus -s $login_usr_pwd<<EOF
set heading off
UPDATE motc.motom_sales_orders_tbl
SET process_flag='C'
,last_updated_by = $conc_user_id
,last_update_date = sysdate
WHERE process_flag='P'
AND email_sent = 'N'
AND order_ref_number IN (
SELECT oola.orig_sys_line_ref
FROM
oe_order_lines_all oola
,oe_order_headers_all ooha
WHERE
ooha.header_id=oola.header_id
AND ooha.orig_sys_document_ref LIKE 'XELUS%'
AND ooha.creation_date>=TRUNC(SYSDATE));
COMMIT;
EXIT
EOF
# Connecting to DB to get the imported order number
touch $MOTONT_TOP/bin/sqllog
chmod 777 $MOTONT_TOP/bin/sqllog
sqlplus -s $login_usr_pwd<<EOF > $MOTONT_TOP/bin/sqllog
set heading off
SELECT ooha.order_number
FROM oe_order_headers_all ooha,
oe_order_lines_all oel,
motc.motom_sales_orders_tbl sot
WHERE ooha.orig_sys_document_ref LIKE 'XELUS%'
AND oel.header_id = ooha.header_id
AND oel.orig_sys_line_ref = sot.order_ref_number
AND sot.email_sent = 'N'
AND ooha.creation_date>=TRUNC(SYSDATE)
order by order_number desc;
EXIT
EOF
if [ -s $MOTONT_TOP/bin/sqllog ]
then
grep "no rows selected" $MOTONT_TOP/bin/sqllog
if [ $? = 0 ]
then
echo "No Order Created"
else
order_number=`awk -F" " 'NR==2{print $1}' $MOTONT_TOP/bin/sqllog`
echo 'Order Number Imported: '$order_number
# Sending mail notification to users with Imported Order Number
echo 'Order Number: '$order_number | mailx -s 'Order Has Been Created For XELUS System' NA11IXEL@motorolasolutions.com
echo 'Mail Notification Successfully Sent'
# Connecting to DB to update staging table with status as 'C'
sqlplus -s $login_usr_pwd<<EOF
set heading off
UPDATE motc.motom_sales_orders_tbl
SET email_sent='Y'
,last_updated_by = $conc_user_id
,last_update_date = sysdate
WHERE process_flag='C'
AND email_sent = 'N'
AND order_ref_number IN (
SELECT oola.orig_sys_line_ref
FROM
oe_order_lines_all oola
,oe_order_headers_all ooha
WHERE
ooha.header_id=oola.header_id
AND ooha.orig_sys_document_ref LIKE 'XELUS%'
AND ooha.creation_date>=TRUNC(SYSDATE));
COMMIT;
EXIT
EOF
fi
rm $MOTONT_TOP/bin/sqllog
fi
===============================end of order import mail sending===========
===
#---------------------------------------------------------------------------------
# Application : Motorola Order Management
# Program : MOTONT_SALES_ORDER_SH
# Author : tcs
# Date : 28-Jul-2011
# Type of program : Shell
# Description: : Program to Load Sales Order details to Staging table.
# Change History :
# Who When Description Version No.
#---------------------------------------------------------------------------------
# KNW438 28-Jul-2011 Initial Creation 1.0
#---------------------------------------------------------------------------------
# Local variable definitions
LV_REQ_ID=`echo $*|awk '{print $2}'|cut -f2 -d'='`
LV_UIDPWD=`echo $*|awk '{print $3}'|cut -f2 -d'"'`
LV_USER_ID=`echo $*|awk '{print $4}'|cut -f2 -d'='`
LV_FILE_NAME=`echo $*|awk '{print $9}'|cut -f2 -d'"'`
LV_DATA_PATH=`echo $*|awk '{print $10}'|cut -f2 -d'"'`
LV_BAD_PATH=`echo $*|awk '{print $11}'|cut -f2 -d'"'`
LV_Log_PATH=`echo $*|awk '{print $12}'|cut -f2 -d'"'`
TSTAMP=`date '+%Y%m%d%H%M%S'`
echo $LV_DATA_PATH
echo 'data_top: '$DATA_TOP
control_file()
{
LV_CTL_DATA="LOAD DATA
APPEND
INTO TABLE MOTC.MOTOM_SALES_ORDERS_TBL
WHEN (52) = 'A'
TRAILING NULLCOLS
(Record_type POSITION(1:4),
Partnum_ordered POSITION(5:23),
Source_loc POSITION(24:37),
customer_loc POSITION(38:51),
order_type POSITION(52:52),
order_ref_number POSITION(53:72),
due_date POSITION(73:80) date(8) 'YYYYMMDD',
ship_date POSITION(81:88) date(8) 'YYYYMMDD',
confirm_qty POSITION(89:96),
requested_qty POSITION(97:104),
purchase_order_num POSITION(105:124),
reforder_ref_num POSITION(125:144),
mode_of_transport POSITION(145:146),
order_outbound POSITION(147:226),
create_date POSITION(227:234) date(8) 'YYYYMMDD',
source_price POSITION(235:243),
src_price_curr_code POSITION(244:251),
extend_src_price POSITION(252:260),
unyield_order_qty POSITION(261:268),
yield_order_qty POSITION(269:276),
good_asit_gets_flag POSITION(277:277),
endoflife_order_flag POSITION(278:278),
repair_from_part POSITION(279:297),
limited_use_order_qty POSITION(298:305),
yield_confirm_qty POSITION(306:313),
unyield_confirm_qty POSITION(314:321),
creation_date SYSDATE,
process_flag CONSTANT 'I',
last_update_date SYSDATE,
email_sent CONSTANT 'N')"
echo $LV_CTL_DATA > "$MOTFND_TOP/bin/MOTINV_SALES_ORDERS.ctl"
}
control_file
echo "File Name:"$LV_FILE_NAME
if [ -f $LV_DATA_PATH/$LV_FILE_NAME ]
then
if [ -s $LV_DATA_PATH/$LV_FILE_NAME ] #Check for Blank files
then
echo "Starting SQL*Loader"
sqlldr userid=$LV_UIDPWD control=$MOTFND_TOP/bin/MOTINV_SALES_ORDERS.ctl data=$LV_DATA_PATH/$LV_FILE_NAME log=$LV_Log_PATH/$LV_REQ_ID.log bad=$LV_BAD_PATH/$LV_REQ_ID.bad
if [ $? = 0 ] #Check for Sqlloader Execution
then
echo "sqlloader execution completed successfully. \n"
else
echo "sqlloader execution completed with errors. \n"
fi
else
echo "${LV_FILE_NAME} does not contain any data. "
echo "Subject: XELUS Sales Order file does not contain any data" > $MOTINV_TOP/bin/salesorderdata.body
echo "Hi" >> $MOTINV_TOP/bin/salesorderdata.body
echo " " >> $MOTINV_TOP/bin/salesorderdata.body
echo "No Xelus Orders were sent." >> $MOTINV_TOP/bin/salesorderdata.body
echo " " >> $MOTINV_TOP/bin/salesorderdata.body
echo "Thank you," >> $MOTINV_TOP/bin//salesorderdata.body
chmod 755 $MOTINV_TOP/bin/salesorderdata.body
(cat $MOTINV_TOP/bin/salesorderdata.body;)|mail NA11IXEL@motorolasolutions.com
fi
else
echo "${LV_FILE_NAME} file does not exists. "
echo "Subject: XELUS Sales Order file does not exists" > $MOTINV_TOP/bin/salesorderfile.body
echo "Hi" >> $MOTINV_TOP/bin/salesorderfile.body
echo " " >> $MOTINV_TOP/bin/salesorderfile.body
echo "No Xelus Orders were sent." >> $MOTINV_TOP/bin/salesorderfile.body
echo " " >> $MOTINV_TOP/bin/salesorderfile.body
echo "Thank you," >> $MOTINV_TOP/bin//salesorderfile.body
chmod 755 $MOTINV_TOP/bin/salesorderfile.body
(cat $MOTINV_TOP/bin/salesorderfile.body;)|mail NA11IXEL@motorolasolutions.com
fi
echo "**************************************************************************************"
# Removing the data file after loading
rm $LV_DATA_PATH/$LV_FILE_NAME
if [ -s $LV_BAD_PATH/$LV_REQ_ID.bad ]
then
echo "Subject: XELUS Sales Order bad records" > $MOTINV_TOP/bin/salesordermail.body
echo "Hi" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/salesordermail.body
echo "The error list of XELUS Sales order records are attached with this mail" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/salesordermail.body
echo "Please find the attachments" >> $MOTINV_TOP/bin/salesordermail.body
echo " " >> $MOTINV_TOP/bin/mail.body
echo "Thank you" >> $MOTINV_TOP/bin/salesordermail.body
chmod 755 $MOTINV_TOP/bin/salesordermail.body
(cat $MOTINV_TOP/bin/salesordermail.body; uuencode $LV_BAD_PATH/$LV_REQ_ID.bad XELUS_Sales_Order_${LV_REQ_ID}.bad)|mail NA11IXEL@motorolasolutions.com
badcontrol_file()
{
LV_BAD_CTL_DATA="LOAD DATA
APPEND
INTO TABLE MOTC.MOTOM_SALES_ORDERS_BAD_TBL
TRAILING NULLCOLS
(Record_type POSITION(1:4),
Partnum_ordered POSITION(5:23),
Source_loc POSITION(24:37),
customer_loc POSITION(38:51),
order_type POSITION(52:52),
order_ref_number POSITION(53:72),
due_date POSITION(73:80) date(8) 'YYYYMMDD',
ship_date POSITION(81:88) date(8) 'YYYYMMDD',
confirm_qty POSITION(89:96),
requested_qty POSITION(97:104),
purchase_order_num POSITION(105:124),
reforder_ref_num POSITION(125:144),
mode_of_transport POSITION(145:146),
order_outbound POSITION(147:226),
create_date POSITION(227:234) date(8) 'YYYYMMDD',
source_price POSITION(235:243),
src_price_curr_code POSITION(244:251),
extend_src_price POSITION(252:260),
unyield_order_qty POSITION(261:268),
yield_order_qty POSITION(269:276),
good_asit_gets_flag POSITION(277:277),
endoflife_order_flag POSITION(278:278),
repair_from_part POSITION(279:297),
limited_use_order_qty POSITION(298:305),
yield_confirm_qty POSITION(306:313),
unyield_confirm_qty POSITION(314:321),
creation_date SYSDATE,
last_update_date SYSDATE,
email_sent CONSTANT 'N'
)"
echo $LV_BAD_CTL_DATA > "$MOTFND_TOP/bin/MOTINV_SALES_ORDERS_BAD.ctl"
}
badcontrol_file
echo "File Name:"$LV_BAD_PATH/$LV_REQ_ID.bad
echo "Starting SQL*Loader"
sqlldr userid=$LV_UIDPWD control=$MOTFND_TOP/bin/MOTINV_SALES_ORDERS_BAD.ctl data=$LV_BAD_PATH/$LV_REQ_ID.bad log=$LV_Log_PATH/$LV_REQ_ID.log
if [ $? = 0 ] #Check for Sqlloader Execution
then
echo "sqlloader execution completed successfully. \n"
else
echo "sqlloader execution completed with errors. \n"
fi
else
echo "${LV_FILE_NAME} does not contain any Bad data. "
fi
rm $LV_BAD_PATH/$LV_REQ_ID.bad
echo " \n "
echo " \n "
================================end of header==
==============bad table=======================
---------------------------------------------------------------------------------
-- Application : Motorola Order Management
-- Program : MOTOM_SALES_ORDERS_BAD_TBL
-- Author : tcs
-- Date : 28-Jul-2011
-- Type of program : SQl Program
-- Description: : Staging table for Sales Order Bad records.
--Change History :
-- Who When Description Version No.
--------------------------------------------------------------------------------------------
-- KNW438 28-JUL-2011 Initial Creation 1.0
--------------------------------------------------------------------------------------------
CREATE TABLE MOTC.MOTOM_SALES_ORDERS_BAD_TBL
(
RECORD_TYPE VARCHAR2(4 BYTE),
PARTNUM_ORDERED VARCHAR2(19 BYTE),
SOURCE_LOC VARCHAR2(70 BYTE),
CUSTOMER_LOC VARCHAR2(14 BYTE),
ORDER_TYPE VARCHAR2(1 BYTE),
ORDER_REF_NUMBER VARCHAR2(20 BYTE),
DUE_DATE VARCHAR2(20 BYTE),
SHIP_DATE VARCHAR2(20 BYTE),
CONFIRM_QTY VARCHAR2(20 BYTE),
REQUESTED_QTY VARCHAR2(20 BYTE),
PURCHASE_ORDER_NUM VARCHAR2(20 BYTE),
REFORDER_REF_NUM VARCHAR2(20 BYTE),
MODE_OF_TRANSPORT VARCHAR2(2 BYTE),
ORDER_OUTBOUND VARCHAR2(90 BYTE),
CREATE_DATE VARCHAR2(20 BYTE),
SOURCE_PRICE VARCHAR2(9 BYTE),
SRC_PRICE_CURR_CODE VARCHAR2(8 BYTE),
EXTEND_SRC_PRICE VARCHAR2(20 BYTE),
UNYIELD_ORDER_QTY VARCHAR2(20 BYTE),
YIELD_ORDER_QTY VARCHAR2(20 BYTE),
GOOD_ASIT_GETS_FLAG VARCHAR2(1 BYTE),
ENDOFLIFE_ORDER_FLAG VARCHAR2(1 BYTE),
REPAIR_FROM_PART VARCHAR2(19 BYTE),
LIMITED_USE_ORDER_QTY VARCHAR2(20 BYTE),
YIELD_CONFIRM_QTY VARCHAR2(20 BYTE),
UNYIELD_CONFIRM_QTY VARCHAR2(20 BYTE),
CREATED_BY VARCHAR2(20 BYTE),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(20 BYTE),
LAST_UPDATE_DATE DATE,
PROCESS_FLAG VARCHAR2(1 BYTE),
ERR_MSG VARCHAR2(200 BYTE),
EMAIL_SENT VARCHAR2(10 BYTE)
)
TABLESPACE MOTC
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE SYNONYM APPS.MOTOM_SALES_ORDERS_BAD_TBL FOR MOTC.MOTOM_SALES_ORDERS_BAD_TBL;
===========================sales order table===================
---------------------------------------------------------------------------------
-- Application : Motorola Order Management
-- Program : MOTOM_SALES_ORDERS_TBL
-- Author : tcs
-- Date : 28-Jul-2011
-- Type of program : SQl Program
-- Description: : Staging table for Sales Order Program.
--Change History :
-- Who When Description Version No.
----------------------------------------------------------------------------------------------
-- KNW438 28-JUL-2011 Initial Creation 1.0
-----------------------------------------------------------------------------------------------
CREATE TABLE MOTC.MOTOM_SALES_ORDERS_TBL
(
RECORD_TYPE VARCHAR2(4 BYTE),
PARTNUM_ORDERED VARCHAR2(19 BYTE),
SOURCE_LOC VARCHAR2(70 BYTE),
CUSTOMER_LOC VARCHAR2(14 BYTE),
ORDER_TYPE VARCHAR2(1 BYTE),
ORDER_REF_NUMBER VARCHAR2(20 BYTE),
DUE_DATE DATE,
SHIP_DATE DATE,
CONFIRM_QTY NUMBER(8),
REQUESTED_QTY NUMBER(8),
PURCHASE_ORDER_NUM NUMBER(20),
REFORDER_REF_NUM NUMBER(20),
MODE_OF_TRANSPORT VARCHAR2(2 BYTE),
ORDER_OUTBOUND VARCHAR2(90 BYTE),
CREATE_DATE DATE,
SOURCE_PRICE VARCHAR2(9 BYTE),
SRC_PRICE_CURR_CODE VARCHAR2(8 BYTE),
EXTEND_SRC_PRICE NUMBER(6,3),
UNYIELD_ORDER_QTY NUMBER(8),
YIELD_ORDER_QTY NUMBER(8),
GOOD_ASIT_GETS_FLAG VARCHAR2(1 BYTE),
ENDOFLIFE_ORDER_FLAG VARCHAR2(1 BYTE),
REPAIR_FROM_PART VARCHAR2(19 BYTE),
LIMITED_USE_ORDER_QTY NUMBER(8),
YIELD_CONFIRM_QTY NUMBER(8),
UNYIELD_CONFIRM_QTY NUMBER(8),
CREATED_BY NUMBER,
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
PROCESS_FLAG VARCHAR2(1 BYTE),
ERR_MSG VARCHAR2(200 BYTE),
EMAIL_SENT VARCHAR2(10 BYTE)
)
TABLESPACE MOTC
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE SYNONYM APPS.MOTOM_SALES_ORDERS_TBL FOR MOTC.MOTOM_SALES_ORDERS_TBL;
===============================
No comments:
Post a Comment