CREATE OR REPLACE PACKAGE BODY APPS.cmgom070_sales_adj_pck
AS
/******************************************************************************
NAME: cmgom070_sales_adj_pck
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 03/06/2007 Senthilkumar Palanisamy CMGOM070 - Updated Sales Adjustments to OM - 2007 Enhancements .
1.5 06/01/2007 Senthilkumar Palanisamy hdr_rec.adjustment_type IN ('SNC', 'SCR','DRW') included to find Return Sales Orders
1.8 07/22/2009 Uday T SCR 1348- Added new Audit Order Types
1.9 07/24/2014 Sunitha Rani Modified for Foriegn Enhancement
1.10 12/22/2014 Vijay - Foreing Enhancement SCR 8385
1.11 03/03/2015 Uday T SCR 8442- Last return format to be changed
******************************************************************************/
CURSOR val_cur
IS
SELECT ACCOUNT_NUMBER,
CUSTOMER_REF_NBR,
ADJUSTMENT_TYPE,
BIPAD,
ISSUE_CODE,
APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE) iss_profit
FROM CMGOM070_STG_TBL; --Modified for Foriegn Enhancement
CURSOR hdr_cur
IS
SELECT ACCOUNT_NUMBER,
CUSTOMER_REF_NBR,
ADJUSTMENT_TYPE,
APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE) iss_profit
FROM CMGOM070_STG_TBL
-- where account_number = p_account_nr
GROUP BY ACCOUNT_NUMBER,
CUSTOMER_REF_NBR,
ADJUSTMENT_TYPE,
APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE); --Modified for Foriegn Enhancement
CURSOR GEN_CUR (p_account_nr IN VARCHAR2)
IS
SELECT ACCOUNT_NUMBER,
CUSTOMER_REF_NBR,
ADJUSTMENT_TYPE,
LPAD (BIPAD, 5, '0') BIPAD,
ISSUE_CODE,
LINE_NUMBER,
UPC,
SUM (copies) quantity
--LPAD (BIPAD, 5, '0') BIPAD, ISSUE_CODE, COPIES
FROM CMGOM070_STG_TBL
WHERE ACCOUNT_NUMBER = P_ACCOUNT_NR
GROUP BY ACCOUNT_NUMBER,
CUSTOMER_REF_NBR,
ADJUSTMENT_TYPE,
LPAD (BIPAD, 5, '0'),
ISSUE_CODE,
LINE_NUMBER,
UPC;
CURSOR main_cur
IS
SELECT rac.customer_id,
rac.customer_number,
c.account_number,
LPAD (c.bipad, 5, 0) bipad,
inventory_item_id,
issue_code,
c.adjustment_type
FROM mtl_system_items msi,
ra_customers rac,
cmgom070_stg_tbl c,
apps.ra_addresses_all addr
WHERE LPAD (c.bipad, 5, 0) = msi.attribute6
AND msi.organization_id = cmg_utility_pck.org_id ('HDG')
AND addr.attribute1 = c.account_number
AND rac.customer_id = addr.customer_id
AND rac.attribute1 = 'MO'
AND addr.ship_to_flag IN ('Y', 'P')
GROUP BY rac.customer_id,
rac.customer_number,
c.account_number,
LPAD (c.bipad, 5, 0),
msi.inventory_item_id,
c.issue_code,
c.adjustment_type;
CURSOR customer_cur (
p_account_nr IN VARCHAR2)
IS
SELECT RC.CUSTOMER_NAME,
CCA.PRICE_LIST_ID,
CCA.ORDER_TYPE_ID,
rc.customer_id,
rc.sales_channel_code,
l.profitcenter_id,
-- rc.attribute1 source
addr.attribute1 SOURCE
FROM apps.cmg_customer_attributes_vw cca,
ra_customers rc, --Modified for Foriegn Enhancement
RA_ADDRESSES ADDR,
apps.cmg_logo_mapping_vw l
WHERE cca.customer_id = rc.customer_id
AND addr.attribute1 = p_account_nr
AND RC.CUSTOMER_ID = ADDR.CUSTOMER_ID
AND SUBSTR (rc.customer_name, 1, 1) = l.customer_name --Modified for Foriegn Enhancement
AND rc.attribute1 = 'MO'
AND addr.ship_to_flag IN ('Y', 'P');
CURSOR dup_customer_cur (
p_account_nr IN VARCHAR2)
IS
SELECT COUNT (*) ac_count
FROM apps.cmg_customer_attributes_vw cca,
ra_customers rc,
ra_addresses addr
WHERE cca.customer_id = rc.customer_id
AND addr.attribute1 = p_account_nr
AND rc.customer_id = addr.customer_id
AND rc.attribute1 = 'MO'
AND addr.ship_to_flag IN ('Y', 'P');
CURSOR addr_site_cur (
p_customer_id IN NUMBER,
p_ship_to_account_nr IN VARCHAR2)
IS
SELECT s1.site_use_id bill_to_id,
s2.site_use_id ship_to_id,
s1.address_id bill_add_id,
s2.address_id ship_add_id
FROM ra_site_uses s1,
ra_site_uses s2,
ra_addresses a1,
ra_addresses a2,
ra_customers c
WHERE c.customer_id = a1.customer_id
AND c.customer_id = a2.customer_id
AND a1.bill_to_flag IN ('Y', 'P')
AND a2.ship_to_flag IN ('Y', 'P')
AND a2.attribute1 = p_ship_to_account_nr
AND a1.address_id = s1.address_id
AND a2.address_id = s2.address_id
AND s1.status = 'A'
AND s2.status = 'A'
AND s1.site_use_code = 'BILL_TO'
AND s2.site_use_code = 'SHIP_TO'
AND c.customer_id = p_customer_id;
CURSOR salesrep_id_cur
IS
SELECT salesrep_id
FROM apps.ra_salesreps
WHERE UPPER (NAME) = 'NO SALES CREDIT';
/* CURSOR PROFITCENTER (p_bipad_isbn IN VARCHAR2, p_issue_code IN VARCHAR2)
IS
select distinct i.profitcenter from
FROM CMG_PPA_TITLE_DIM_TBL T, CMG_PPA_ISSUE_DIM_TBL I
WHERE T.TITLE_DIM_ID = I.TITLE_DIM_ID
AND T.BIPAD = P_BIPAD_ISBN
and i.issue_code = p_issue_code;
*/
CURSOR line_item_cur (
p_iss_profit VARCHAR2) --Modified for Foriegn Enhancement
IS
SELECT LPAD (bipad, 5, '0') bipad,
issue_code,
copies,
APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE)
line_iss_profit
FROM CMGOM070_STG_TBL
WHERE APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE) =
p_iss_profit
GROUP BY bipad, issue_code, copies;
CURSOR item_cur (
p_bipad_isbn IN VARCHAR2,
p_issue_code IN VARCHAR2)
IS
SELECT DISTINCT
msi.inventory_item_id,
rev.attribute7,
MSI.PRIMARY_UOM_CODE,
l.profitcenter_id, --Modified for Foriegn Enhancement
rev.attribute8 cover_date, -- added 10/6/2014
TO_CHAR (rev.effectivity_date, 'DD-MON-YYYY') on_sale_date, --added 10/6/2014
rev.attribute11 off_sale_date, --added 10/6/2014
rev.attribute12 last_return_date, -- added 10/6/2014
msi.returnable_flag returnable_flag -- added 10/6/2014
FROM MTL_SYSTEM_ITEMS MSI,
MTL_ITEM_REVISIONS REV,
PO.PO_VENDORS P, --Modified for Foriegn Enhancement
apps.cmg_logo_mapping_vw l
WHERE rev.attribute7 = p_issue_code
AND msi.inventory_item_id = rev.inventory_item_id
AND msi.organization_id = rev.organization_id
AND msi.attribute6 = p_bipad_isbn
AND msi.inventory_item_status_code = 'HDG ACTIVE'
AND NVL (rev.attribute1, 'N') = 'N'
AND MSI.ORGANIZATION_ID = CMG_UTILITY_PCK.ORG_ID ('HDG')
AND REV.attribute6 = TO_CHAR (p.vendor_id)
AND p.attribute13 = l.profitcenter_id;
CURSOR netsale_cur (
p_customer_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_issue_code IN VARCHAR2)
IS
SELECT sol.unit_selling_price,
line_id,
SUBSTR (sol.attribute5, 1, 10) cover_price,
sol.attribute2 cover_date,
sol.attribute3 edition,
sol.attribute6 on_sale_date,
sol.attribute7 off_sale_date,
sol.attribute8 last_return_date,
sol.attribute9 returnable_status,
sol.pricing_attribute1 upc
FROM oe_order_lines sol, oe_order_headers soh
WHERE sol.unit_selling_price > 0
AND NVL (sol.attribute13, 'xx') <> 'OBSOLETE'
AND sol.attribute1 = p_issue_code
AND sol.inventory_item_id = p_inventory_item_id
AND sol.ship_from_org_id = cmg_utility_pck.org_id ('HDG')
AND soh.ordered_date >= ADD_MONTHS (TRUNC (SYSDATE), -36)
AND sol.header_id = soh.header_id
AND soh.order_category_code = 'ORDER'
AND soh.sold_to_org_id = p_customer_id
AND ROWNUM = 1;
CURSOR netsale_return_cur (
p_customer_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_issue_code IN VARCHAR2)
IS
SELECT sol.unit_selling_price,
line_id,
SUBSTR (sol.attribute5, 1, 10) cover_price,
sol.attribute2,
sol.attribute3 edition,
sol.attribute6 on_sale_date,
sol.attribute7 off_sale_date,
sol.attribute8 last_return_date,
sol.attribute9 returnable_status,
sol.pricing_attribute1 upc
FROM oe_order_lines sol, oe_order_headers soh
WHERE NVL (sol.attribute13, 'xx') <> 'OBSOLETE'
-- and sol.unit_selling_price >0
AND sol.attribute1 = p_issue_code
AND sol.inventory_item_id = p_inventory_item_id
AND sol.ship_from_org_id = cmg_utility_pck.org_id ('HDG')
AND sol.header_id = soh.header_id
AND soh.sold_to_org_id = p_customer_id
AND soh.order_category_code = 'RETURN'
-- AND months_between(trunc(sysdate),soh.ordered_date ) <= 36
AND soh.ordered_date >= ADD_MONTHS (TRUNC (SYSDATE), -36)
AND ROWNUM = 1;
-- ORDER BY sol.unit_selling_price desc;
CURSOR order_source_cur
IS
SELECT order_source_id
FROM apps.oe_order_sources
WHERE NAME = 'HDG MANUAL ADJ';
CURSOR any_edition_cur (
p_inventory_item_id IN NUMBER,
p_issue_code IN VARCHAR2)
IS
SELECT attribute13 edition, attribute10 upc
FROM mtl_item_revisions rev
WHERE rev.inventory_item_id = p_inventory_item_id
AND rev.organization_id = cmg_utility_pck.org_id ('HDG')
AND rev.attribute7 = p_issue_code
AND NVL (rev.attribute1, 'N') = 'N'
AND ROWNUM = 1;
CURSOR line_cur (
p_account_number IN NUMBER,
p_customer_ref_nbr IN VARCHAR2,
p_adjustment_type IN VARCHAR2,
p_iss_profit VARCHAR2 --number to varchar2 vijay 11/17/2014
) --Modified for Foriegn Enhancement
IS
SELECT LPAD (bipad, 5, '0') bipad,
issue_code,
line_number,
upc,
APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE),
SUM (copies) quantity
FROM cmgom070_stg_tbl
WHERE account_number = p_account_number
AND customer_ref_nbr = p_customer_ref_nbr
AND adjustment_type = p_adjustment_type
AND APPS.get_isspc_fnc (LPAD (BIPAD, 5, '0'), ISSUE_CODE) =
p_iss_profit
GROUP BY LPAD (bipad, 5, '0'),
issue_code,
line_number,
upc;
CURSOR user_order_adj_type_cur (
p_uesr_adjustment_type IN VARCHAR2)
IS
SELECT ffvl.description sales_adjust_upload_type
FROM fnd_flex_values_vl ffvl, fnd_flex_value_sets ffvs
WHERE ffvl.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = 'CMG_SALES_ADJ_UPLOAD_TYPES'
AND ffvl.flex_value = p_uesr_adjustment_type;
-- CURSOR user_order_type_like_cur (
-- p_user_order_adj_type IN VARCHAR2,
-- p_order_type_id IN NUMBER
-- )
-- IS
-- SELECT SUBSTR (NAME,
-- 1,
-- INSTR (NAME, '-', 1, 1) - 1
-- )
-- || '%'
-- || p_user_order_adj_type
-- || '%'
---- || SUBSTR (NAME, INSTR (NAME, '-', -1, 1) + 1, LENGTH (NAME))
-- || SUBSTR (NAME, INSTR (NAME, '-', -1, 2) + 1, 2)
-- derived_user_adj_type
-- FROM oe_transaction_types
-- WHERE end_date_active IS NULL
-- AND NVL (end_date_active, SYSDATE) >= SYSDATE
-- AND transaction_type_id = p_order_type_id;
/* Changed the Cursor to accept the new Audit Order Types ---SCR 1348*/
CURSOR user_order_type_like_cur (
p_user_order_adj_type IN VARCHAR2,
p_order_type_id IN NUMBER)
IS
SELECT order_category_code,
SUBSTR (NAME,
1,
INSTR (NAME,
'-',
1,
1)
- 1)
|| '%'
|| DECODE (
p_user_order_adj_type,
'AUDIT RETURN', '-' || p_user_order_adj_type || '-',
'AUDIT RETURN REVERSAL', '-'
|| p_user_order_adj_type
|| '-',
'AUDIT DRAW', '-' || p_user_order_adj_type || '-',
'AUDIT DRAW REVERSAL', '-' || p_user_order_adj_type || '-',
'-' || p_user_order_adj_type) --SCR 1348
|| '%'
|| SUBSTR (NAME,
INSTR (NAME,
'-',
-1,
2)
+ 1,
2)
|| '%'
derived_user_adj_type --Modified for Foriegn Enhancement
--SUBSTR (NAME,INSTR (NAME, '-', 1, 3) - 2,2) derived_user_adj_type
FROM apps.oe_transaction_types
WHERE end_date_active IS NULL
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND transaction_type_id = p_order_type_id;
CURSOR user_order_type_derived_cur (
p_user_order_adj_type IN VARCHAR2)
IS
SELECT NAME,
order_category_code,
transaction_type_id,
accounting_rule_id,
invoicing_rule_id,
conversion_type_code,
currency_code
FROM apps.OE_TRANSACTION_TYPES
WHERE NAME LIKE p_user_order_adj_type --H%-AUDIT RETURN REVERSAL-%CN
AND end_date_active IS NULL
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND NAME NOT LIKE '%TRANSFER%'
AND NAME NOT LIKE '%PRICE CHANGE%';
-- cursor to capture foreign extra days vijay 12/15/2014
CURSOR foreign_cur (
p_customer_id IN NUMBER)
IS
SELECT a.attribute14 extra_days --, rc.customer_name customer_name
FROM AR_CUSTOMER_PROFILES_V a, ra_customers rc
WHERE a.customer_id = rc.customer_id
AND a.customer_id = p_customer_id
AND ROWNUM < 2;
/* -- cursor to capture profitcenters vijay 12/16/2014
CURSOR prof_cur (
p_cust_name in VARCHAR2)
IS
SELECT profitcenter
FROM cmg_logo_mapping_vw
WHERE customer_name = substr(p_cust_name,1,1);
*/
v_bill_to_site_use_id NUMBER;
v_ship_to_site_use_id NUMBER;
v_bill_address_id NUMBER;
v_ship_address_id NUMBER;
v_sales_channel_code VARCHAR2 (50);
netsale_rec netsale_cur%ROWTYPE;
v_accounting_rule_id NUMBER;
v_invoicing_rule_id NUMBER;
v_ord_source_id NUMBER;
v_conversion_type_code VARCHAR2 (20);
v_currency_code VARCHAR2 (10);
v_salesrep_id NUMBER;
customer_rec customer_cur%ROWTYPE;
dup_customer_rec dup_customer_cur%ROWTYPE;
skip_header EXCEPTION;
v_order_type_id NUMBER;
v_original_system_ref VARCHAR2 (100);
v_order_type VARCHAR2 (40);
v_customer_ref_nr VARCHAR2 (50);
v_edition VARCHAR2 (10) := NULL; -- =null added by vijay 10/7/2014
v_current_line_nr NUMBER := 0;
v_conversion_code VARCHAR2 (20);
v_error_flag VARCHAR2 (1) := 'N';
v_user_id NUMBER DEFAULT fnd_global.user_id;
v_sysdate DATE DEFAULT SYSDATE;
v_error_log VARCHAR2 (100);
p_errbuf VARCHAR2 (80);
p_retcode NUMBER;
v_dirpath VARCHAR2 (100);
v_filepath VARCHAR2 (100);
v_path VARCHAR2 (100);
v_status VARCHAR2 (80);
v_req_id NUMBER DEFAULT (fnd_global.conc_request_id);
v_user_name VARCHAR2 (80);
v_logfile UTL_FILE.file_type;
v_request NUMBER;
v_request_status BOOLEAN;
v_errbuf VARCHAR2 (80);
v_retcode VARCHAR2 (80);
v_phase VARCHAR2 (100);
v_dev_phase VARCHAR2 (100);
v_dev_status VARCHAR2 (100);
v_message VARCHAR2 (100);
ud_loader_error EXCEPTION;
ud_no_file_found EXCEPTION;
ud_dup_records EXCEPTION;
ud_item_not_found EXCEPTION;
ud_customer_not_found EXCEPTION;
ud_order_not_found EXCEPTION;
ud_negative_copies_found EXCEPTION;
ud_order_type_not_found EXCEPTION;
ud_upc_price_not_found EXCEPTION;
ud_data_validation_error_found EXCEPTION;
UD_ISSUEBIPAD_NOT_FOUND EXCEPTION;
v_inventory_item_id NUMBER;
v_transaction_type_id NUMBER;
v_item_uom mtl_system_items.primary_uom_code%TYPE;
v_issue_code VARCHAR2 (6);
v_count NUMBER := 0;
v_ret_msg_txt VARCHAR2 (100);
v_dphase VARCHAR2 (100);
v_dstatus VARCHAR2 (100);
v_order_ajustment_type VARCHAR2 (100);
v_upc VARCHAR2 (12) := NULL; --=null added by vijay 10/7/2014
v_order_catg_code VARCHAR2 (50);
v_o_price_list_id NUMBER;
v_o_price_list_name VARCHAR2 (150);
v_cover_price NUMBER;
v_start_date DATE;
v_end_date DATE;
v_result_cover_price NUMBER;
V_RESULT_SELLING_PRICE NUMBER;
v_result_discount NUMBER;
V_RESULT_DISC_AMOUNT NUMBER;
v_profitcenter NUMBER;
user_order_adj_type_rec user_order_adj_type_cur%ROWTYPE;
user_order_type_derived_rec user_order_type_derived_cur%ROWTYPE;
user_order_type_like_rec user_order_type_like_cur%ROWTYPE;
netsale_return_rec netsale_return_cur%ROWTYPE;
v_column_error_check NUMBER DEFAULT (0);
v_header_error_check NUMBER DEFAULT (0);
v_lines_error_check NUMBER DEFAULT (0);
v_update_error_check NUMBER DEFAULT (0);
v_iss_profit NUMBER;
v_cover_date VARCHAR2 (100); -- added 10/6/2014
v_on_sale_date DATE; -- added 10/6/2014
v_off_sale_date VARCHAR2 (100); -- added 10/6/2014
v_last_return_date VARCHAR2 (100); --added 10/6/2014
v_returnable_flag MTL_SYSTEM_ITEMS.returnable_flag%TYPE; --added 12/9/2014
--profit_center_check boolean :=FALSE; --added 10/6/2014
item_rec item_cur%ROWTYPE;
v_extra_days AR_CUSTOMER_PROFILES_V.attribute14%TYPE; --added 12/16/2014
--v_cust_name ra_customers.customer_name%TYPE; --added 12/16/2014
--v_profitcenter_name cmg_logo_mapping_vw.profitcenter%TYPE; --added 12/16/2014
PROCEDURE main_prc (p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
p_filename IN VARCHAR2)
IS
BEGIN
cmg_utility_pck.write_output (
'+--------------------------------------' || '------------+');
cmg_utility_pck.write_output ('Hearst Applications');
cmg_utility_pck.write_output ('Copyright (c) Comag Marketing Group');
cmg_utility_pck.write_output (
'+-------------------------------------' || '--------------+');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+-------------------------------------' || '--------------+');
cmg_utility_pck.write_output (' ');
fnd_profile.get ('HDG_INCOMING_PATH', v_path);
v_filepath := v_path || '/' || p_filename;
hdg_submit_request_pck.hdg_sub_req_prc (
'HDG',
'CMGOM070_LDR',
'CMGOM070 Upload Sales Adjustments Loader Program',
v_filepath,
v_status);
IF v_status <> 'SUCCESS'
THEN
RAISE ud_loader_error;
END IF;
OPEN order_source_cur;
FETCH order_source_cur INTO v_ord_source_id;
CLOSE order_source_cur;
-- Validation 2 - Check Account Number/Customer is present in the Customer setup or not
-- Start - 101
/* FOR SPLIT_REC IN SPLIT_CUR
LOOP
cmg_utility_pck.write_output (SUBSTR(SPLIT_REC.CUSTOMER_NAME,1,1));
IF (SUBSTR(SPLIT_REC.CUSTOMER_NAME,1,1) = 'G') THEN
--cmgom070_sales_adj_pck.GEN_PRC(split_rec.account_number,
--v_errbuf,
--v_retcode
--);
null;
ELSE */
FOR VAL_REC IN val_cur
LOOP --Modified for Foriegn Enhancement
IF VAL_rec.iss_profit = 'NO ISSUE PROFITCENTER'
THEN
cmg_utility_pck.write_output (
'Invalid issue/bipad: Bipad: '
|| VAL_REC.bipad
|| ' '
|| 'Issue_code :'
|| VAL_REC.issue_code);
v_error_flag := 'Y';
-- RAISE UD_ISSUEBIPAD_NOT_FOUND;
END IF;
END LOOP;
FOR hdr_rec IN hdr_cur --Modified for Foriegn Enhancement
LOOP
--101
OPEN customer_cur (hdr_rec.account_number);
FETCH customer_cur INTO customer_rec;
IF customer_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Customer Account Number not found for '
|| hdr_rec.account_number);
v_error_flag := 'Y';
END IF;
IF customer_cur%ROWCOUNT > 1
THEN
cmg_utility_pck.write_output (
'This Account Number Assigned to Multiple Customers '
|| hdr_rec.account_number);
v_error_flag := 'Y';
END IF;
CLOSE customer_cur;
OPEN dup_customer_cur (hdr_rec.account_number);
FETCH dup_customer_cur INTO dup_customer_rec;
IF dup_customer_rec.ac_count > 1
THEN
cmg_utility_pck.write_output (
'This Account Number Assigned to Multiple Customers '
|| hdr_rec.account_number);
v_error_flag := 'Y';
END IF;
CLOSE dup_customer_cur;
END LOOP;
-- end loop;-- 101
-- Validation 2 - Check Account Number/Customer is present in the Customer setup or not
-- End- 101
-- Validation 5a - Get the Adjustment Order Type based on the Ajustment Type in the file and Order Type
-- start 105a
BEGIN
-- FOR SPLIT_REC IN SPLIT_CUR-- 105 a
-- LOOP
FOR hdr_rec IN hdr_cur
LOOP
--BEGIN
IF hdr_rec.iss_profit = 'NO ISSUE PROFITCENTER' --added vijay 11/17/2014
THEN
v_iss_profit := NULL;
ELSE
v_iss_profit := hdr_rec.iss_profit; --Modified for Foriegn Enhancement
END IF;
OPEN user_order_adj_type_cur (hdr_rec.adjustment_type);
FETCH user_order_adj_type_cur INTO user_order_adj_type_rec;
CLOSE user_order_adj_type_cur;
OPEN user_order_type_like_cur (user_order_adj_type_rec.sales_adjust_upload_type,
customer_rec.order_type_id);
FETCH user_order_type_like_cur INTO user_order_type_like_rec;
CLOSE user_order_type_like_cur;
--Modified for Foriegn Enhancement
IF (v_iss_profit <> customer_rec.profitcenter_id)
THEN -- hdr_rec.iss_profit changed to v_iss_profit vijay 11/17/2014
OPEN user_order_type_derived_cur (user_order_type_like_rec.derived_user_adj_type);
FETCH user_order_type_derived_cur
INTO user_order_type_derived_rec;
IF user_order_type_derived_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Could not derive the Order Type for Sales Adjustment Type / Accountfor(2) '
|| hdr_rec.adjustment_type
|| '/'
|| hdr_rec.account_number);
RAISE ud_order_type_not_found;
END IF;
CLOSE user_order_type_derived_cur;
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM apps.oe_transaction_types t,
hdg.gen_profitcenter_mapping_tbl m
WHERE t.name LIKE
M.DERIVED_ORDERTYPE
|| SUBSTR (t.name, INSTR (t.name, '-', 2))
|| '%'
AND m.WHOLESALER_PROFITCENTER_ID =
customer_rec.profitcenter_id
AND m.ISSUE_PROFITCENTER_ID = hdr_rec.iss_profit
AND t.name LIKE
'%' || SUBSTR(user_order_type_derived_rec.name,5)||'%'; -- SCR 8591
-- || user_order_adj_type_rec.sales_adjust_upload_type
-- || '-%';
/*select transaction_type_id
into v_transaction_type_id
from apps.oe_transaction_types t,
hdg.gen_ordertypes_mapping_tbl m,
apps.cmg_logo_mapping_vw l
where t.name=decode(v_profitcenter,l.profitcenter_id,m.name||l.customer_name||m.attribute1)
and l.profitcenter_id=v_profitcenter
and m.name like 'G-MO-'||user_order_adj_type_rec.sales_adjust_upload_type||'-%'
and m.attribute1='FN'
and m.attribute2 is null;
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_column_error_check := v_column_error_check + 1;
v_lines_error_check := v_lines_error_check + 1;
cmg_utility_pck.write_output ('');
-- cmg_utility_pck.write_output(' Error in record '||
-- v_details_rec.rownum);
cmg_utility_pck.write_output (
' Order type not available '
|| 'for the customer '
|| customer_rec.customer_name);
END;
ELSE
OPEN user_order_type_derived_cur (user_order_type_like_rec.derived_user_adj_type);
FETCH user_order_type_derived_cur
INTO user_order_type_derived_rec;
V_TRANSACTION_TYPE_ID :=
user_order_type_derived_rec.transaction_type_id;
IF user_order_type_derived_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Could not derive Order Type from Sales Adjustment Type for the account: '
|| hdr_rec.account_number);
v_error_flag := 'Y';
END IF;
CLOSE user_order_type_derived_cur;
END IF;
--END IF;
END LOOP; --Modified for Foriegn Enhancement
-- end loop;
END;
-- 105a
-- Validation 5a - Get the Adjustment Order Type based on the Ajustment Type in the file and Order Type
-- End 105a
-- Validation 3 - Check bipad and Issue codes are present in the Item setup
-- Start 102
FOR line_item_rec IN line_item_cur (v_iss_profit)
LOOP
IF line_item_rec.copies < 0
THEN
cmg_utility_pck.write_output (
'Negative Copies Found for the'
|| ' Bipad: '
|| line_item_rec.bipad
|| ' '
|| ' Issue_code : '
|| line_item_rec.issue_code);
v_error_flag := 'Y';
END IF;
-- 102
OPEN item_cur (line_item_rec.bipad, line_item_rec.issue_code);
FETCH ITEM_CUR
INTO v_inventory_item_id,
v_issue_code,
v_item_uom,
v_profitcenter,
v_cover_date,
v_on_sale_date,
v_off_sale_date,
v_last_return_date, --added by vijay 10/6/2013
v_returnable_flag; -- added by vijay 12/9/2014
IF item_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Bipad/Issue Not Present In Item Setup. '
|| ' Bipad: '
|| line_item_rec.bipad
|| ' '
|| ' Issue_code :'
|| line_item_rec.issue_code);
v_error_flag := 'Y';
END IF;
CLOSE item_cur;
END LOOP; -- 102
-- Validation 3 - Check bipad and Issue codes are present in the Item setup
-- End 102
-- Validation 9 - Check for the Existing of Sales Order and UPC for the given Customer Id, Invenroty Item Id and Issue codes
-- Start 109
-- FOR SPLIT_REC IN SPLIT_CUR
-- LOOP
FOR hdr_rec IN hdr_cur
LOOP
-- 109a
OPEN customer_cur (hdr_rec.account_number);
FETCH customer_cur INTO customer_rec;
CLOSE customer_cur;
FOR line_rec IN line_cur (hdr_rec.account_number,
hdr_rec.customer_ref_nbr,
hdr_rec.adjustment_type,
hdr_rec.iss_profit) --Modified for Foriegn Enhancement
LOOP -- 109b
OPEN ITEM_CUR (LINE_REC.BIPAD, LINE_REC.ISSUE_CODE);
FETCH ITEM_CUR
INTO v_inventory_item_id,
v_issue_code,
v_item_uom,
v_profitcenter, --Modified for Foriegn Enhancement
v_cover_date,
v_on_sale_date,
v_off_sale_date,
v_last_return_date, --added by vijay 10/6/2014
v_returnable_flag; -- added by vijay 12/9/2014
IF item_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Item not found for bipad/issue code: '
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
END IF;
CLOSE item_cur;
OPEN netsale_cur (customer_rec.customer_id,
v_inventory_item_id,
line_rec.issue_code);
FETCH netsale_cur INTO netsale_rec;
IF netsale_cur%NOTFOUND AND line_rec.upc IS NULL
THEN
IF hdr_rec.adjustment_type IN
('SNC', 'SCR', 'DRW', 'RRV', 'ADW', 'ARV') -- SCR 1348 ( Added the New audit adjustment types)
THEN
OPEN netsale_return_cur (customer_rec.customer_id,
v_inventory_item_id,
line_rec.issue_code);
FETCH netsale_return_cur INTO netsale_return_rec;
IF netsale_return_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Original Return Order not found for Adjustment Type/Acccount/Bipad/Issue : '
|| hdr_rec.adjustment_type
|| '-'
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
v_error_flag := 'Y';
END IF;
CLOSE netsale_return_cur;
ELSE
cmg_utility_pck.write_output (
'Original Sales Order not found for Adjustment Type/Acccount/Bipad/Issue : '
|| hdr_rec.adjustment_type
|| '-'
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
v_error_flag := 'Y';
END IF;
ELSE
IF netsale_rec.edition IS NULL
THEN
OPEN any_edition_cur (v_inventory_item_id,
line_rec.issue_code);
FETCH any_edition_cur
INTO v_edition, v_upc;
CLOSE any_edition_cur;
END IF;
IF hdr_rec.adjustment_type IN ('SCR', 'DRW', 'ADW', 'MRC')
AND line_rec.upc IS NULL -- SCR 1348 ( Added the New audit adjustment types)
THEN
IF netsale_rec.cover_price = 0
OR netsale_rec.cover_price IS NULL
THEN
IF netsale_rec.upc IS NULL
THEN
cmg_utility_pck.write_output (
'UPC Not found for this SCR Type Adjustments Account/Bipad/Issue : '
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
v_error_flag := 'Y';
END IF;
END IF;
END IF;
END IF;
CLOSE netsale_cur;
END LOOP;
-- 109b -- line
END LOOP;
-- end loop;
-- 109a -- hdr_cur
-- Validation 9 - Check for the Existing of Sales Order and UPC for the given Customer Id, Invenroty Item Id and Issue codes
-- End 109
IF v_error_flag = 'Y'
THEN
RAISE ud_data_validation_error_found;
END IF;
--FOR SPLIT_REC IN SPLIT_CUR
--loop
FOR hdr_rec IN hdr_cur
LOOP
OPEN customer_cur (hdr_rec.account_number);
FETCH customer_cur INTO customer_rec;
CLOSE customer_cur;
OPEN addr_site_cur (customer_rec.customer_id, customer_rec.SOURCE);
FETCH addr_site_cur
INTO v_bill_to_site_use_id,
v_ship_to_site_use_id,
v_bill_address_id,
v_ship_address_id;
IF addr_site_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Address Info not found for the Account(2) #: '
|| hdr_rec.account_number);
END IF;
CLOSE addr_site_cur;
OPEN salesrep_id_cur;
FETCH salesrep_id_cur INTO v_salesrep_id;
CLOSE salesrep_id_cur;
v_customer_ref_nr := hdr_rec.customer_ref_nbr;
v_original_system_ref :=
'SAJ-'
|| hdr_rec.account_number
|| '-'
|| hdr_rec.customer_ref_nbr
|| hdr_rec.iss_profit --Modified for Foriegn Enhancement
|| '-'
|| hdr_rec.adjustment_type
|| '-'
|| SYSDATE;
-- Validation 5 - Get the Adjustment Order Type based on the Ajustment Type in the file and Order Type
-- start 105
BEGIN -- 105
-- cmg_utility_pck.write_output ( 'Account No: '
-- || hdr_rec.account_number
-- || ' Adjustment Type: '
-- || hdr_rec.adjustment_type
-- );
OPEN user_order_adj_type_cur (hdr_rec.adjustment_type);
FETCH user_order_adj_type_cur INTO user_order_adj_type_rec;
CLOSE user_order_adj_type_cur;
OPEN user_order_type_like_cur (user_order_adj_type_rec.sales_adjust_upload_type,
customer_rec.order_type_id);
FETCH user_order_type_like_cur INTO user_order_type_like_rec;
CLOSE user_order_type_like_cur;
-- cmg_utility_pck.write_output
-- ( 'Account No: '
-- || hdr_rec.account_number
-- || ' Derived Adjustment Type: '
-- || user_order_adj_type_rec.sales_adjust_upload_type
-- || ' Derived Adj Type: '
-- || user_order_type_like_rec.derived_user_adj_type
-- );
--Modified for Foriegn Enhancement
IF (hdr_rec.iss_profit <> customer_rec.profitcenter_id)
THEN
OPEN user_order_type_derived_cur (user_order_type_like_rec.derived_user_adj_type);
FETCH user_order_type_derived_cur
INTO user_order_type_derived_rec;
IF user_order_type_derived_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Could not derive the Order Type for Sales Adjustment Type / Accountfor(2) '
|| hdr_rec.adjustment_type
|| '/'
|| hdr_rec.account_number);
RAISE ud_order_type_not_found;
END IF;
CLOSE user_order_type_derived_cur;
BEGIN
SELECT transaction_type_id
INTO v_transaction_type_id
FROM apps.oe_transaction_types t,
hdg.gen_profitcenter_mapping_tbl m
WHERE t.name LIKE
M.DERIVED_ORDERTYPE
|| SUBSTR (t.name, INSTR (t.name, '-', 2))
|| '%'
AND m.WHOLESALER_PROFITCENTER_ID =
customer_rec.profitcenter_id
AND m.ISSUE_PROFITCENTER_ID = hdr_rec.iss_profit
AND t.name LIKE
'%'
|| SUBSTR(user_order_type_derived_rec.name,5)||'%'; --SCR 8591
-- || user_order_adj_type_rec.sales_adjust_upload_type
-- || '-%';
/*select transaction_type_id
into v_transaction_type_id
from apps.oe_transaction_types t,
hdg.gen_ordertypes_mapping_tbl m,
apps.cmg_logo_mapping_vw l
where t.name=decode(v_profitcenter,l.profitcenter_id,m.name||l.customer_name||m.attribute1)
and l.profitcenter_id=v_profitcenter
and m.name like 'G-MO-'||user_order_adj_type_rec.sales_adjust_upload_type||'-%'
and m.attribute1='FN'
and m.attribute2 is null;
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_column_error_check := v_column_error_check + 1;
v_lines_error_check := v_lines_error_check + 1;
cmg_utility_pck.write_output ('');
-- cmg_utility_pck.write_output(' Error in record '||
-- v_details_rec.rownum);
cmg_utility_pck.write_output (
' Order type not available '
|| 'for the customer '
|| customer_rec.customer_name);
END;
ELSE
OPEN user_order_type_derived_cur (user_order_type_like_rec.derived_user_adj_type);
FETCH user_order_type_derived_cur
INTO user_order_type_derived_rec;
V_TRANSACTION_TYPE_ID :=
user_order_type_derived_rec.transaction_type_id;
IF user_order_type_derived_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Could not derive the Order Type for Sales Adjustment Type / Accountfor(2) '
|| hdr_rec.adjustment_type
|| '/'
|| hdr_rec.account_number);
RAISE ud_order_type_not_found;
END IF;
CLOSE user_order_type_derived_cur;
END IF; --Modified for Foriegn Enhancement
END; -- 105
-- Validation 5 - Get the Adjustment Order Type based on the Ajustment Type in the file and Order Type
-- End 105
INSERT INTO oe_headers_interface (creation_date,
created_by,
last_update_date,
last_updated_by,
orig_sys_document_ref,
sold_to_org_id,
order_type_id,
order_category,
ordered_date,
transactional_curr_code,
order_source_id,
price_list_id,
salesrep_id,
invoice_customer_id,
ship_to_customer_id,
invoice_to_org_id,
ship_to_org_id,
-- invoice_address_id,
-- ship_address_id,
agreement_id,
customer_payment_term_id,
booked_flag,
accounting_rule_id,
invoicing_rule_id,
sales_channel_code,
conversion_type_code,
customer_po_number,
CONTEXT,
attribute1,
attribute2,
attribute3,
ready_flag,
rejected_flag,
operation_code,
return_reason_code)
VALUES (
SYSDATE,
v_user_id,
SYSDATE,
v_user_id,
v_original_system_ref,
customer_rec.customer_id,
V_TRANSACTION_TYPE_ID, --user_order_type_derived_rec.transaction_type_id,--Modified for Foriegn Enhancement
--v_order_type_id,
user_order_type_derived_rec.order_category_code,
--v_order_catg_code, --'ORDER',
SYSDATE,
user_order_type_derived_rec.currency_code,
--v_currency_code,
v_ord_source_id,
customer_rec.price_list_id,
v_salesrep_id,
customer_rec.customer_id,
customer_rec.customer_id,
v_bill_to_site_use_id,
v_ship_to_site_use_id,
-- v_bill_address_id,
-- v_ship_address_id,
NULL, -- hdr_rec.agreement_id,
5, -- Immeditate
'Y',
user_order_type_derived_rec.accounting_rule_id,
--v_accounting_rule_id,
user_order_type_derived_rec.invoicing_rule_id,
--v_invoicing_rule_id,
v_sales_channel_code,
user_order_type_derived_rec.conversion_type_code,
--v_conversion_type_code,
v_customer_ref_nr,
user_order_type_derived_rec.order_category_code,
--v_order_catg_code , -- 'ORDER',
NULL,
NULL,
v_customer_ref_nr,
'Y',
'N',
'CREATE',
DECODE (
user_order_type_derived_rec.order_category_code,
'RETURN', 'RETURN',
NULL));
-- FOR line_rec IN line_cur(hdr_rec.customer_number,hdr_rec.customer_ref_nr)
v_current_line_nr := 0;
FOR line_rec IN line_cur (hdr_rec.account_number,
hdr_rec.customer_ref_nbr,
hdr_rec.adjustment_type,
hdr_rec.iss_profit)
LOOP
OPEN item_cur (line_rec.bipad, line_rec.issue_code);
FETCH ITEM_CUR
--INTO item_rec;
INTO v_inventory_item_id,
v_issue_code,
v_item_uom,
v_profitcenter, --Modified for Foriegn Enhancement
v_cover_date,
v_on_sale_date,
v_off_sale_date,
v_last_return_date, --added by vijay10/6/2014
v_returnable_flag; -- added by vijay 12/9/2014
IF item_cur%NOTFOUND
THEN
cmg_utility_pck.write_output ('ITEM NOT FOUND ');
END IF;
--CLOSE item_cur; --modified by vijay
OPEN netsale_cur (customer_rec.customer_id,
v_inventory_item_id,
line_rec.issue_code);
FETCH netsale_cur INTO netsale_rec;
-- cmg_utility_pck.write_output (
-- 'Cover_date' || netsale_rec.cover_date || ' ');
IF netsale_cur%NOTFOUND
AND line_rec.upc IS NULL
AND hdr_rec.adjustment_type NOT IN
('SNC', 'SCR', 'DRW', 'RRV', 'ADW', 'ARV') -- SCR 1348 ( Added the New audit adjustment types)
-- IF 901 -- START
THEN
cmg_utility_pck.write_output (
'Original order not found for Account/Bipad/Issue(2) : '
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
RAISE ud_order_not_found;
-- added - 09-May-07 - by Senthil - added to handle SNC and SNR Adjustments types -- Start
ELSIF netsale_cur%NOTFOUND
AND line_rec.upc IS NULL
AND hdr_rec.adjustment_type IN
('SNC', 'SCR', 'DRW', 'RRV', 'ADW', 'ARV') -- SCR 1348 ( Added the New audit adjustment types)
THEN
OPEN netsale_return_cur (customer_rec.customer_id,
v_inventory_item_id,
line_rec.issue_code);
FETCH netsale_return_cur INTO netsale_rec;
IF netsale_return_cur%NOTFOUND
THEN
cmg_utility_pck.write_output (
'Original Return Order not found for Adjustment Type/Acccount/Bipad/Issue(2) : '
|| hdr_rec.adjustment_type
|| '-'
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
RAISE ud_order_not_found;
END IF;
-- v_sncsnr_cover_price := netsale_return_rec.cover_price ;
-- CLOSE netsale_return_cur;
-- added - 09-May-07 - by Senthil - added to handle SNC and SNR Adjustments types - End ;
-- ELSE -- commented by senthil - 09-may-07
END IF; -- IF 901 -- END
IF netsale_rec.edition IS NULL
THEN
OPEN any_edition_cur (v_inventory_item_id, line_rec.issue_code);
FETCH any_edition_cur
INTO v_edition, v_upc;
CLOSE any_edition_cur;
END IF;
IF hdr_rec.adjustment_type = 'SNC'
THEN
IF netsale_rec.cover_price = 0
OR netsale_rec.cover_price IS NULL
THEN
OPEN any_edition_cur (v_inventory_item_id,
line_rec.issue_code);
FETCH any_edition_cur
INTO v_edition, v_upc;
CLOSE any_edition_cur;
cmg_price_disc_pck.calculate_list_price_prc (
customer_rec.price_list_id,
v_inventory_item_id,
v_upc,
v_o_price_list_id,
v_o_price_list_name,
v_cover_price,
v_start_date,
v_end_date);
v_result_selling_price := 0;
v_result_cover_price := NVL (v_cover_price, 0);
ELSE
v_result_selling_price := 0;
v_result_cover_price := NVL (netsale_rec.cover_price, 0);
END IF;
ELSIF hdr_rec.adjustment_type IN
('RRV', 'MRC', 'KIL', 'ARV', 'ART', 'AKL') -- SCR 1348 ( Added the New audit adjustment types)
THEN
v_result_cover_price := netsale_rec.cover_price;
-- Added by Senthil - 09-May-07 - Start
IF ( v_result_cover_price = 0
OR v_result_cover_price IS NULL)
AND line_rec.upc IS NOT NULL
THEN
cmg_price_disc_pck.calculate_list_price_prc (
customer_rec.price_list_id,
v_inventory_item_id,
line_rec.upc,
v_o_price_list_id,
v_o_price_list_name,
v_cover_price,
v_start_date,
v_end_date);
v_result_cover_price := v_cover_price;
cmg_price_disc_pck.calculate_discount_prc (
v_o_price_list_id,
v_inventory_item_id,
v_result_discount,
v_result_disc_amount);
-- v_result_discount := p_discount;
-- billingprice = Coverprice - (coverprice*discount/100)
-- v_result_selling_price :=
-- ( v_result_cover_price
-- - (v_result_cover_price * v_result_discount / 100)
-- );
IF v_result_discount IS NOT NULL
THEN
v_result_selling_price :=
(ROUND (
v_result_cover_price
- ( v_result_cover_price
* (v_result_discount / 100)),
5));
ELSE
v_result_selling_price := v_result_disc_amount;
END IF;
ELSE
v_result_cover_price := netsale_rec.cover_price;
v_result_selling_price := netsale_rec.unit_selling_price;
END IF;
--Modified for Foriegn Enhancement
ELSIF hdr_rec.adjustment_type IN ('SCR', 'DRW', 'ADW', 'MRC') -- SCR 1348 ( Added the New audit adjustment types)
THEN
DBMS_OUTPUT.put_line (netsale_rec.cover_price);
IF netsale_rec.cover_price = 0
OR netsale_rec.cover_price IS NULL
THEN
IF line_rec.upc IS NOT NULL
THEN
DBMS_OUTPUT.put_line (line_rec.upc);
cmg_price_disc_pck.calculate_list_price_prc (
customer_rec.price_list_id,
v_inventory_item_id,
line_rec.upc,
v_o_price_list_id,
v_o_price_list_name,
v_cover_price,
v_start_date,
v_end_date);
v_result_cover_price := v_cover_price;
cmg_price_disc_pck.calculate_discount_prc (
v_o_price_list_id,
v_inventory_item_id,
v_result_discount,
v_result_disc_amount);
-- v_result_selling_price :=
-- ( v_result_cover_price
-- - (v_result_cover_price * v_result_discount / 100)
-- );
IF v_result_discount IS NOT NULL
THEN
v_result_selling_price :=
(ROUND (
v_result_cover_price
- ( v_result_cover_price
* (v_result_discount / 100)),
5));
ELSE
v_result_selling_price := v_result_disc_amount;
END IF;
ELSIF line_rec.upc IS NULL AND netsale_rec.upc IS NOT NULL
THEN
cmg_price_disc_pck.calculate_list_price_prc (
customer_rec.price_list_id,
v_inventory_item_id,
netsale_rec.upc, --line_rec.upc,--Modified for Foriegn Enhancement
v_o_price_list_id,
v_o_price_list_name,
v_cover_price,
v_start_date,
v_end_date);
v_result_cover_price := v_cover_price;
cmg_price_disc_pck.calculate_discount_prc (
v_o_price_list_id,
v_inventory_item_id,
v_result_discount,
v_result_disc_amount);
-- v_result_selling_price :=
-- ( v_result_cover_price
-- - (v_result_cover_price * v_result_discount / 100)
-- );
IF v_result_discount IS NOT NULL
THEN
v_result_selling_price :=
(ROUND (
v_result_cover_price
- ( v_result_cover_price
* (v_result_discount / 100)),
5));
ELSE
v_result_selling_price := v_result_disc_amount;
END IF;
ELSIF line_rec.upc IS NULL AND netsale_rec.upc IS NULL
THEN
cmg_utility_pck.write_output (
'UPC Not found to calculate unit and selling price for this '
|| hdr_rec.adjustment_type
|| ' Type Adjustments Account/Bipad/Issue : '
|| hdr_rec.account_number
|| '-'
|| line_rec.bipad
|| '-'
|| line_rec.issue_code);
RAISE ud_upc_price_not_found;
END IF;
ELSE
v_result_cover_price := netsale_rec.cover_price;
v_result_selling_price := netsale_rec.unit_selling_price;
END IF;
END IF;
-- capture last return date change vijay 12/16/2014
OPEN foreign_cur (customer_rec.customer_id);
FETCH foreign_cur INTO v_extra_days;
--OPEN prof_cur (v_cust_name);
--FETCH prof_cur INTO v_profitcenter_name;
-- cmg_utility_pck.write_output('v_last_return_date BEFORE'|| v_last_return_date);
IF v_extra_days > 0
THEN
v_last_return_date :=
TO_DATE (v_last_return_date) + v_extra_days;
END IF;
CLOSE foreign_cur;
--CLOSE prof_cur;
--
-- cmg_utility_pck.write_output('v_last_return_date'|| v_last_return_date);
v_current_line_nr := NVL (v_current_line_nr, 0) + 1;
INSERT INTO oe_lines_interface (creation_date,
created_by,
last_update_date,
last_updated_by,
orig_sys_document_ref,
orig_sys_line_ref,
line_number,
-- line_type,
order_quantity_uom,
ordered_quantity,
unit_selling_price,
unit_list_price,
request_date,
inventory_item_id,
sold_to_org_id,
ship_from_org_id,
accounting_rule_id,
invoicing_rule_id,
order_source_id,
ship_to_org_id,
tax_code,
calculate_price_flag,
pricing_context,
pricing_attribute1,
CONTEXT,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
pricing_date,
rejected_flag,
operation_code,
industry_attribute19)
VALUES (
SYSDATE,
v_user_id,
SYSDATE,
v_user_id,
v_original_system_ref,
v_current_line_nr,
v_current_line_nr,
-- 'REGULAR',
v_item_uom,
line_rec.quantity,
-- netsale_rec.unit_selling_price,
-- netsale_rec.unit_selling_price,
ROUND (v_result_selling_price, 5),
v_result_cover_price,
SYSDATE,
v_inventory_item_id,
customer_rec.customer_id,
cmg_utility_pck.org_id ('HDG'),
v_accounting_rule_id,
v_invoicing_rule_id,
v_ord_source_id,
v_ship_to_site_use_id,
NULL,
-- line_rec.tax_code,
'N', -- calculate_price_flag
'Upgrade Context', -- pricing context
NVL (NVL (line_rec.upc, netsale_rec.upc), v_upc),
NULL,
line_rec.issue_code,
NVL (v_cover_date, netsale_rec.cover_date), --netsale_rec.cover_date changed to nvl(..) 10/6/2014 by vijay
NVL (v_edition, netsale_rec.edition), -- NVL (netsale_rec.edition,v_edition) flipped by vijay 10/7/2014
line_rec.bipad,
-- netsale_rec.cover_price,
v_result_cover_price,
NVL (TO_CHAR (v_on_sale_date, 'DD-MON-YYYY'),
netsale_rec.on_sale_date), --netsale_rec.on_sale_date changed to nvl(..) 10/6/2014
NVL (v_off_sale_date, netsale_rec.off_sale_date), --netsale_rec.off_sale_date changed to nvl(..) 10/6/2014
-- NVL (v_last_return_date,netsale_rec.last_return_date), --netsale_rec.last_return_date changed to nvl(..) 10/6/2014
NVL (
TO_CHAR (
TO_DATE (v_last_return_date, 'DD-MON-YY'),
'DD-MON-YYYY'),
netsale_rec.last_return_date), --SCR 8442
NVL (v_returnable_flag,
netsale_rec.returnable_status), --netsale_rec.returnable_status changed to nvl(..) 12/9/2014
SYSDATE,
'N',
'CREATE',
netsale_rec.line_id);
INSERT INTO oe_price_atts_interface (created_by,
creation_date,
last_updated_by,
last_update_date,
operation_code,
orig_sys_document_ref,
orig_sys_line_ref,
pricing_context,
pricing_attribute1,
order_source_id,
flex_title)
VALUES (v_user_id,
SYSDATE,
v_user_id,
SYSDATE,
'CREATE',
v_original_system_ref,
v_current_line_nr,
'Upgrade Context',
NVL (line_rec.upc, netsale_rec.upc),
v_ord_source_id,
'QP_ATTR_DEFNS_PRICING');
v_count := v_count + 1;
-- END IF; -- commented by Senthil - 09-may-07
v_edition := NULL; --added by vijay 10/7/2014
v_upc := NULL; --added by vijay 10/7/2014
--added by vijay 10/6/2014
IF item_cur%ISOPEN
THEN
CLOSE item_cur;
END IF;
-- added by Senthil 09-may-2007 - Start
IF netsale_return_cur%ISOPEN
THEN
CLOSE netsale_return_cur;
END IF;
IF netsale_cur%ISOPEN
THEN
CLOSE netsale_cur;
END IF;
-- added by Senthil 09-may-2007 - End
UPDATE cmgom070_stg_tbl
SET interface_flag = 'Y',
last_update_date = SYSDATE,
last_updated_by = v_user_id
WHERE line_number = line_rec.line_number;
v_result_cover_price := NULL;
v_result_selling_price := NULL;
END LOOP; -- line
END LOOP;
-- END IF;
-- END LOOP;-- hdr_cur
IF v_count > 0
THEN
hdg_ret_global_pck.hdg_order_import_prc (v_retcode,
v_ret_msg_txt,
v_dphase,
v_dstatus,
v_ord_source_id,
v_error_log,
3);
END IF;
cmg_utility_pck.write_output ('');
cmg_utility_pck.write_output (
'Total No of Records Processed :' || v_count);
p_retcode := SQLCODE;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: Completed' || p_errbuf;
COMMIT;
EXCEPTION
WHEN ud_data_validation_error_found
THEN
p_retcode := 2;
p_errbuf :=
'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_DATA_VALIDATION_ERROR_FOUND';
cmg_utility_pck.write_output (
' Data Validation Error occured, please check the data and rerun the program');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent request ' || 'completed successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_no_file_found
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_NO_FILE_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent request ' || 'completed successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN UD_ISSUEBIPAD_NOT_FOUND
THEN
p_retcode := 2;
p_retcode := 2;
p_errbuf :=
'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_ISSUEBIPAD_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent request ' || 'completed successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
--DELETE from cmgom070_stg_tbl;
WHEN ud_negative_copies_found
THEN
p_retcode := 2;
p_errbuf :=
'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_NEGATIVE_COPIES_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent request ' || 'completed successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_loader_error
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_LOADER_ERROR';
cmg_utility_pck.write_output (
'The sql Loader failed '
|| 'to load the data. Check the'
|| ' log file for that process');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_item_not_found
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_ITEM_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Correct The Data File ' || 'And Run The Program Again');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_customer_not_found
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_CUSTOMER_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Correct The Data File ' || 'And Run The Program Again');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_order_not_found
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_ORDER_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_upc_price_not_found
THEN
p_retcode := 2;
p_errbuf := 'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_UPC_PRICE_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Correct The Data File and/or check the UPC and Price setup for this Order Type '
|| 'And Run The Program Again');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN ud_order_type_not_found
THEN
p_retcode := 2;
p_errbuf :=
'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: UD_ORDER_TYPE_NOT_FOUND';
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Correct The Data File and/or check the setup for this Order Type '
|| 'And Run The Program Again');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
WHEN OTHERS
THEN
p_retcode := SQLCODE;
p_errbuf :=
SUBSTR (
'CMGOM070_SALES_ADJ_PCK.MAIN_PRC: Err Others = > ' || SQLERRM,
1,
150);
cmg_utility_pck.write_output (SQLERRM);
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'Concurrent Request ' || 'Completed Successfully');
cmg_utility_pck.write_output (
'Current System Time is : '
|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MM:SS'));
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output (
'+----------------------' || '-----------------------------+');
ROLLBACK;
END MAIN_PRC;
END cmgom070_sales_adj_pck;
/
