AS
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 (
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;
/
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;
/
CREATE OR REPLACE PACKAGE BODY APPS.hdg_submit_request_pck AS
PROCEDURE hdg_sub_req_prc(p_application IN VARCHAR2 DEFAULT('HDG'),
p_program IN VARCHAR2,
p_description IN VARCHAR2,
p_filepath_name IN VARCHAR2,
p_dev_status OUT VARCHAR2) AS
v_req_id NUMBER;
v_call_status BOOLEAN;
v_phase VARCHAR2(100);
v_status VARCHAR2(100);
v_dev_phase VARCHAR2(100);
v_dev_status VARCHAR2(100);
v_message VARCHAR2(100);
BEGIN
v_req_id := FND_REQUEST.SUBMIT_REQUEST(p_application ,
p_program ,
p_description ,
'' ,
FALSE ,
p_filepath_name);
if v_req_id <> 0 then
commit;
end if;
-- v_status ='Error' was added by Ravi Maddali on May 6th 2005, to check the Error Condition
Loop
v_call_status := FND_CONCURRENT.GET_REQUEST_STATUS(v_req_id,
null,
null,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
EXIT WHEN v_dev_phase = 'COMPLETE' or v_status ='Error';
END LOOP;
IF v_dev_phase = 'COMPLETE' AND v_dev_status = 'NORMAL' THEN
p_dev_status := 'SUCCESS';
ELSE
p_dev_status := 'FAILED';
END IF;
END hdg_sub_req_prc;
END hdg_submit_request_pck;
/
function submit_request (
application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE,
argument1 IN varchar2 default CHR(0),
argument2 IN varchar2 default CHR(0),
argument3 IN varchar2 default CHR(0),
argument4 IN varchar2 default CHR(0),
argument5 IN varchar2 default CHR(0),
argument6 IN varchar2 default CHR(0),
argument7 IN varchar2 default CHR(0),
argument8 IN varchar2 default CHR(0),
argument9 IN varchar2 default CHR(0),
argument10 IN varchar2 default CHR(0),
argument11 IN varchar2 default CHR(0),
argument12 IN varchar2 default CHR(0),
argument13 IN varchar2 default CHR(0),
argument14 IN varchar2 default CHR(0),
argument15 IN varchar2 default CHR(0),
argument16 IN varchar2 default CHR(0),
argument17 IN varchar2 default CHR(0),
argument18 IN varchar2 default CHR(0),
argument19 IN varchar2 default CHR(0),
argument20 IN varchar2 default CHR(0),
argument21 IN varchar2 default CHR(0),
argument22 IN varchar2 default CHR(0),
argument23 IN varchar2 default CHR(0),
argument24 IN varchar2 default CHR(0),
argument25 IN varchar2 default CHR(0),
argument26 IN varchar2 default CHR(0),
argument27 IN varchar2 default CHR(0),
argument28 IN varchar2 default CHR(0),
argument29 IN varchar2 default CHR(0),
argument30 IN varchar2 default CHR(0),
argument31 IN varchar2 default CHR(0),
argument32 IN varchar2 default CHR(0),
argument33 IN varchar2 default CHR(0),
argument34 IN varchar2 default CHR(0),
argument35 IN varchar2 default CHR(0),
argument36 IN varchar2 default CHR(0),
argument37 IN varchar2 default CHR(0),
argument38 IN varchar2 default CHR(0),
argument39 IN varchar2 default CHR(0),
argument40 IN varchar2 default CHR(0),
argument41 IN varchar2 default CHR(0),
argument42 IN varchar2 default CHR(0),
argument43 IN varchar2 default CHR(0),
argument44 IN varchar2 default CHR(0),
argument45 IN varchar2 default CHR(0),
argument46 IN varchar2 default CHR(0),
argument47 IN varchar2 default CHR(0),
argument48 IN varchar2 default CHR(0),
argument49 IN varchar2 default CHR(0),
argument50 IN varchar2 default CHR(0),
argument51 IN varchar2 default CHR(0),
argument52 IN varchar2 default CHR(0),
argument53 IN varchar2 default CHR(0),
argument54 IN varchar2 default CHR(0),
argument55 IN varchar2 default CHR(0),
argument56 IN varchar2 default CHR(0),
argument57 IN varchar2 default CHR(0),
argument58 IN varchar2 default CHR(0),
argument59 IN varchar2 default CHR(0),
argument60 IN varchar2 default CHR(0),
argument61 IN varchar2 default CHR(0),
argument62 IN varchar2 default CHR(0),
argument63 IN varchar2 default CHR(0),
argument64 IN varchar2 default CHR(0),
argument65 IN varchar2 default CHR(0),
argument66 IN varchar2 default CHR(0),
argument67 IN varchar2 default CHR(0),
argument68 IN varchar2 default CHR(0),
argument69 IN varchar2 default CHR(0),
argument70 IN varchar2 default CHR(0),
argument71 IN varchar2 default CHR(0),
argument72 IN varchar2 default CHR(0),
argument73 IN varchar2 default CHR(0),
argument74 IN varchar2 default CHR(0),
argument75 IN varchar2 default CHR(0),
argument76 IN varchar2 default CHR(0),
argument77 IN varchar2 default CHR(0),
argument78 IN varchar2 default CHR(0),
argument79 IN varchar2 default CHR(0),
argument80 IN varchar2 default CHR(0),
argument81 IN varchar2 default CHR(0),
argument82 IN varchar2 default CHR(0),
argument83 IN varchar2 default CHR(0),
argument84 IN varchar2 default CHR(0),
argument85 IN varchar2 default CHR(0),
argument86 IN varchar2 default CHR(0),
argument87 IN varchar2 default CHR(0),
argument88 IN varchar2 default CHR(0),
argument89 IN varchar2 default CHR(0),
argument90 IN varchar2 default CHR(0),
argument91 IN varchar2 default CHR(0),
argument92 IN varchar2 default CHR(0),
argument93 IN varchar2 default CHR(0),
argument94 IN varchar2 default CHR(0),
argument95 IN varchar2 default CHR(0),
argument96 IN varchar2 default CHR(0),
argument97 IN varchar2 default CHR(0),
argument98 IN varchar2 default CHR(0),
argument99 IN varchar2 default CHR(0),
argument100 IN varchar2 default CHR(0))
return number is
reqid number;
begin
-- If not in database trigger mode:
-- Rollback to start_of_submission if submit() function fails to
-- submit the request.
if (not (P_DB_TRIGGER_MODE)) then
savepoint start_of_submission;
end if;
reqid := submit (
application, program, description, start_time, sub_request,
Argument1, Argument2, Argument3, Argument4, Argument5,
Argument6, Argument7, Argument8, Argument9, Argument10,
Argument11, Argument12, Argument13, Argument14, Argument15,
Argument16, Argument17, Argument18, Argument19, Argument20,
Argument21, Argument22, Argument23, Argument24, Argument25,
Argument26, Argument27, Argument28, Argument29, Argument30,
Argument31, Argument32, Argument33, Argument34, Argument35,
Argument36, Argument37, Argument38, Argument39, Argument40,
Argument41, Argument42, Argument43, Argument44, Argument45,
Argument46, Argument47, Argument48, Argument49, Argument50,
Argument51, Argument52, Argument53, Argument54, Argument55,
Argument56, Argument57, Argument58, Argument59, Argument60,
Argument61, Argument62, Argument63, Argument64, Argument65,
Argument66, Argument67, Argument68, Argument69, Argument70,
Argument71, Argument72, Argument73, Argument74, Argument75,
Argument76, Argument77, Argument78, Argument79, Argument80,
Argument81, Argument82, Argument83, Argument84, Argument85,
Argument86, Argument87, Argument88, Argument89, Argument90,
Argument91, Argument92, Argument93, Argument94, Argument95,
Argument96, Argument97, Argument98, Argument99, Argument100);
if (not (P_DB_TRIGGER_MODE)) then
if (reqid = 0) then
rollback to start_of_submission;
end if;
end if;
init_pvt_vars;
return (reqid);
end submit_request;
