ORACLE MASTERMINDS: Oracle Blanket Sales Agreement Loader API

Wednesday 26 September 2018

Oracle Blanket Sales Agreement Loader API

CREATE OR REPLACE PACKAGE XX_BSA_LOAD_PKG
IS
--=================
-- Global Variables
--=================
gn_request_id NUMBER := apps.fnd_global.conc_request_id;
gn_prog_appl_id NUMBER := apps.fnd_global.prog_appl_id;
gn_responsibility_id NUMBER := apps.fnd_global.resp_id;
gn_respappl_id NUMBER := apps.fnd_global.resp_appl_id;
gn_program_id NUMBER := apps.fnd_global.conc_program_id;
gn_user_id NUMBER := apps.fnd_global.user_id;
gn_login_id NUMBER := apps.fnd_global.login_id;
gn_business_group_id NUMBER := apps.fnd_global.per_business_group_id;
gd_sysdate DATE := SYSDATE;
p_api_version NUMBER := 1.0;
PROCEDURE main(x_errbuf OUT VARCHAR2, x_retcode OUT VARCHAR2);

PROCEDURE trim_data;

PROCEDURE prevalidation;

PROCEDURE load_bsa;

PROCEDURE record_status;

FUNCTION validate_line_dates(l_item VARCHAR2,l_source_contract VARCHAR2) RETURN VARCHAR2;

END XX_BSA_LOAD_PKG;
/

CREATE OR REPLACE PACKAGE BODY XX_BSA_LOAD_PKG
IS
gc_status VARCHAR2 (1);
g_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');
gn_bulk_limit NUMBER;
gc_ret_status VARCHAR2 (1);
gn_bulk_err NUMBER;
gn_conc_req_id CONSTANT VARCHAR2 (10) := fnd_global.conc_request_id;

-- +====================================================================================+
-- |
-- |
-- +====================================================================================+
-- | |
-- | $Id: XX_BSA_LOAD_PKG 12/06/2018 |
-- | |
-- | |
-- |Description : Program to create blanket sales agreement |
-- | |
-- | |
-- | |
-- |Change History: |
-- |--------------- |
-- |Version Date Author Remarks |
-- |------- ---------- ------------ ------------------- |
-- | 1.0 12/06/2018 Mohan G Program to create Blanket Sales Agreement
-- +====================================================================================+
--
PROCEDURE main (x_errbuf OUT VARCHAR2, x_retcode OUT VARCHAR2)
IS
BEGIN
fnd_file.put_line
(apps.fnd_file.LOG,
'###########################################################################################'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');
fnd_file.put_line (apps.fnd_file.LOG,
' ----- Main Program Started -----'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');
XX_BSA_LOAD_PKG.trim_data;
XX_BSA_LOAD_PKG.prevalidation;
XX_BSA_LOAD_PKG.load_bsa;
fnd_file.put_line (apps.fnd_file.LOG, ' ');
fnd_file.put_line (apps.fnd_file.LOG,
' ----- Main Program Completed -----'
);
fnd_file.put_line (apps.fnd_file.LOG, ' ');
INSERT INTO xx_om_bsa_stg_a select * from xx_om_bsa_stg
WHERE NVL(PROCESS_FLAG,'~') <> 'N';
DELETE FROM xx_om_bsa_stg WHERE NVL(PROCESS_FLAG,'~') <> 'N';
COMMIT;
XX_BSA_LOAD_PKG.record_status;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Exception Main Program ');
fnd_file.put_line (fnd_file.LOG,'Error info for main program: ' || SQLERRM);
END main;

-------------------------------------------------------------------
-- Procedure to Validate and Create Blanket Sales agreement Records
-------------------------------------------------------------------
PROCEDURE load_bsa
AS
p_init_msg_list VARCHAR2 (200) := NULL;
p_commit VARCHAR2 (200) := NULL;
p_validation_level NUMBER := 0;
l_msg VARCHAR2 (200) := NULL;
lc_count NUMBER := 0;
l_sold_to_org_id NUMBER;
l_order_type_id NUMBER := 0;
l_hdr_rec oe_blanket_pub.header_rec_type;
l_hdr_val_rec oe_blanket_pub.header_val_rec_type;
l_line_tbl oe_blanket_pub.line_tbl_type;
l_line_val_tbl oe_blanket_pub.line_val_tbl_type;
l_line_rec oe_blanket_pub.line_rec_type;
l_line_val_rec oe_blanket_pub.line_val_rec_type;
l_control_rec oe_blanket_pub.control_rec_type;
n_line_counter NUMBER := 0;
l_ship_from_org_id NUMBER := 0;
l_inv_to_org_id NUMBER := 0;
l_ship_to_orgid NUMBER := 0;
l_inv_item_id NUMBER := 0;
l_line_ship_to_orgid NUMBER := 0;
l_line_inv_to_org_id NUMBER := 0;
l_uom VARCHAR2 (200) := NULL;
l_line_ship_from_org_id NUMBER := 0;
l_invo_rule_id NUMBER := 0;
l_acct_rule_id NUMBER := 0;
-- output variables
x_line_tbl oe_blanket_pub.line_tbl_type;
x_header_rec oe_blanket_pub.header_rec_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_return_status VARCHAR2 (30);

CURSOR lcu_bsa_stg_hdr
IS
SELECT customer_number, customer_po, commencement_date,
expiration_date, month_processed, contract_end_date,
ship_to_location, data_centre, bill_to_location,
source_contract_no
FROM xx_om_bsa_stg
WHERE process_flag = 'V'
GROUP BY customer_number,
customer_po,
commencement_date,
expiration_date,
month_processed,
contract_end_date,
ship_to_location,
data_centre,
bill_to_location,
source_contract_no
ORDER BY source_contract_no;

CURSOR lcu_bsa_stg_lns (
pcust_no VARCHAR2,
pcust_po VARCHAR2,
p_cdate VARCHAR2,
p_expdate VARCHAR2,
p_mproc VARCHAR2,
p_cend_date VARCHAR2,
p_shiptoloc VARCHAR2,
p_datacen VARCHAR2,
p_bill_to_loc VARCHAR2,
p_s_cont_no VARCHAR2
)
IS
SELECT item, line_number, line_activ_date, line_exp_date,
line_shipto, line_data_centre, line_bill_to_loc, pue,
product_code, start_date, termination_date, dsp_flag,
quantity, price, min_order_qty, account_rule,
invoicing_rule, record_id,source_contract_no
FROM xx_om_bsa_stg
WHERE process_flag = 'V'
AND customer_number = pcust_no
AND NVL (customer_po, '~') = NVL (pcust_po, '~')
AND commencement_date = p_cdate
AND NVL (expiration_date, TRUNC(SYSDATE)) = NVL (p_expdate, TRUNC(SYSDATE))
AND month_processed = p_mproc
AND contract_end_date = p_cend_date
AND ship_to_location = p_shiptoloc
AND data_centre = p_datacen
AND bill_to_location = p_bill_to_loc
AND source_contract_no = p_s_cont_no
ORDER BY source_contract_no,line_number;
BEGIN
fnd_file.put_line (fnd_file.LOG,
'Blanket Sales Order Loader Started'
);
fnd_file.put_line
(fnd_file.LOG,
'==============================================================================='
);
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Apps Initialize Started-------------------------'
);
fnd_global.apps_initialize (gn_user_id,
gn_responsibility_id,
gn_respappl_id
);
mo_global.init ('ONT');
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Apps Initialize Completed-------------------------'
);
fnd_file.put_line
(fnd_file.LOG,
'==============================================================================='
);

---------------------------------------
-- Header Loop Started
---------------------------------------
FOR lc_rec IN lcu_bsa_stg_hdr
LOOP
n_line_counter := 0;
l_ship_from_org_id := 0;
l_order_type_id := 0;
l_inv_to_org_id := 0;
l_ship_to_orgid := 0;
l_sold_to_org_id := 0;
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Header Part Started for the source contract number-------------------------'
|| lc_rec.source_contract_no
);

BEGIN
SELECT organization_id
INTO l_ship_from_org_id
FROM org_organization_definitions
WHERE organization_code = lc_rec.data_centre;
EXCEPTION
WHEN OTHERS
THEN
l_ship_from_org_id := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid data centre or ware house.'
|| lc_rec.data_centre
);
END;

BEGIN
SELECT transaction_type_id
INTO l_order_type_id
FROM oe_transaction_types_tl
WHERE 1 = 1 AND UPPER (NAME) = 'AGREEMENT'
AND LANGUAGE = 'US';
EXCEPTION
WHEN OTHERS
THEN
l_order_type_id := -1;
fnd_file.put_line (fnd_file.LOG, 'Invalid Order type.');
END;
BEGIN
SELECT csu.site_use_id
INTO l_inv_to_org_id
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec.bill_to_location
AND csu.site_use_code = 'BILL_TO';
EXCEPTION
WHEN OTHERS
THEN
l_inv_to_org_id := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid invoice to org id.'
|| lc_rec.customer_number
);
END;

BEGIN
SELECT csu.site_use_id
INTO l_ship_to_orgid
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec.ship_to_location
AND csu.site_use_code = 'SHIP_TO';
EXCEPTION
WHEN OTHERS
THEN
l_ship_to_orgid := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid ship to org id.'
|| lc_rec.customer_number
);
END;

BEGIN
SELECT cust_account_id
INTO l_sold_to_org_id
FROM hz_cust_accounts
WHERE account_number = lc_rec.customer_number AND status = 'A';
EXCEPTION
WHEN OTHERS
THEN
l_sold_to_org_id := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid sold to org id.'
|| lc_rec.customer_number
);
END;

fnd_file.put_line (fnd_file.LOG,
' l_ship_from_org_id-->'
|| l_ship_from_org_id
|| ' l_order_type_id-->'
|| l_order_type_id
|| ' l_inv_to_org_id-->'
|| l_inv_to_org_id
|| ' l_ship_to_orgid-->'
|| l_ship_to_orgid
|| ' l_sold_to_org_id-->'
|| l_sold_to_org_id
);
l_hdr_rec := oe_blanket_pub.g_miss_header_rec;
l_hdr_val_rec := oe_blanket_pub.g_miss_header_val_rec;
l_hdr_rec.operation := oe_globals.g_opr_create;
l_hdr_rec.sold_to_org_id := l_sold_to_org_id;
l_hdr_rec.order_type_id := l_order_type_id;
l_hdr_rec.ship_from_org_id := l_ship_from_org_id;
l_hdr_rec.cust_po_number := lc_rec.customer_po;
l_hdr_rec.invoice_to_org_id := l_inv_to_org_id;
l_hdr_rec.ship_to_org_id := l_ship_to_orgid;
l_hdr_rec.CONTEXT := 'XXXX';
l_hdr_rec.attribute1 := to_char(to_date(lc_rec.month_processed,'MON-YY'),'MON-YY');
l_hdr_rec.attribute2 :=
TO_CHAR (lc_rec.contract_end_date, 'YYYY/MM/DD HH24:MI:SS');
l_hdr_rec.start_date_active := lc_rec.commencement_date;
l_hdr_rec.end_date_active := lc_rec.expiration_date;
l_hdr_rec.attribute3 :=lc_rec.source_contract_no;
---------------------------------------
-- Line Loop Started
---------------------------------------
l_line_rec := oe_blanket_pub.g_miss_blanket_line_rec;
l_line_val_rec := oe_blanket_pub.g_miss_blanket_line_val_rec;
l_line_tbl.delete;
/* fnd_file.put_line (fnd_file.LOG,
' l_line_rec Count is --> ' || l_line_rec.COUNT
);
fnd_file.put_line (fnd_file.LOG,
' l_line_val_rec Count is --> ' || l_line_val_rec.COUNT
);*/
fnd_file.put_line (fnd_file.LOG,
' l_line_tbl Count is --> ' || l_line_tbl.COUNT
);

FOR lc_rec_lines IN lcu_bsa_stg_lns (lc_rec.customer_number,
lc_rec.customer_po,
lc_rec.commencement_date,
lc_rec.expiration_date,
lc_rec.month_processed,
lc_rec.contract_end_date,
lc_rec.ship_to_location,
lc_rec.data_centre,
lc_rec.bill_to_location,
lc_rec.source_contract_no
)
LOOP
fnd_file.put_line
(fnd_file.LOG,
'-----------------------Line Started-------------------------'
);

---------------------------------
-- Getting the inventory item idlc
---------------------------------
BEGIN
SELECT msib.inventory_item_id
INTO l_inv_item_id
FROM mtl_system_items_b msib,
org_organization_definitions ood
WHERE msib.organization_id = ood.organization_id
AND segment1 = lc_rec_lines.item
AND ood.operating_unit = 82
AND ood.ORGANIZATION_CODE = lc_rec_lines.line_data_centre
AND msib.enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_inv_item_id := -1;
fnd_file.put_line (fnd_file.LOG, 'Invalid inventory Item ');
END;

fnd_file.put_line (fnd_file.LOG,
' inventory Item --> ' || l_inv_item_id
);

---------------------------------
-- Getting the Item UOM
---------------------------------
BEGIN
SELECT msib.primary_uom_code
INTO l_uom
FROM mtl_system_items_b msib,
org_organization_definitions ood
WHERE msib.organization_id = ood.organization_id
AND segment1 = lc_rec_lines.item
AND ood.operating_unit = 82
AND ood.ORGANIZATION_CODE = lc_rec_lines.line_data_centre
AND msib.enabled_flag = 'Y';
EXCEPTION
WHEN OTHERS
THEN
l_uom := NULL;
fnd_file.put_line (fnd_file.LOG,
'Invalid inventory Item UOM '
);
END;

---------------------------------------
--- Line level data centre
---------------------------------------
BEGIN
SELECT organization_id
INTO l_line_ship_from_org_id
FROM org_organization_definitions
WHERE organization_code = lc_rec_lines.line_data_centre;
EXCEPTION
WHEN OTHERS
THEN
l_line_ship_from_org_id := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid data centre or ware house.'
);
END;

-----------------------------------------------
-- Line level ship to
---------------------------------------------
BEGIN
SELECT csu.site_use_id
INTO l_line_ship_to_orgid
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec_lines.line_shipto
AND csu.site_use_code = 'SHIP_TO';
EXCEPTION
WHEN OTHERS
THEN
l_line_ship_to_orgid := -1;
fnd_file.put_line (fnd_file.LOG, 'Invalid ship to org id.');
END;

----------------------------------------------------------------
-- Line level Bill To
----------------------------------------------------------------
BEGIN
SELECT csu.site_use_id
INTO l_line_inv_to_org_id
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec_lines.line_bill_to_loc
AND csu.site_use_code = 'BILL_TO';
EXCEPTION
WHEN OTHERS
THEN
l_line_inv_to_org_id := -1;
fnd_file.put_line (fnd_file.LOG,
'Invalid invoice to org id.'
);
END;

fnd_file.put_line (fnd_file.LOG,
' l_inv_item_id --> '
|| l_inv_item_id
|| ' l_line_ship_from_org_id --> '
|| l_line_ship_from_org_id
|| ' l_line_ship_to_orgid --> '
|| l_line_ship_to_orgid
|| ' l_line_inv_to_org_id --> '
|| l_line_inv_to_org_id
);
n_line_counter := n_line_counter + 1;
l_line_tbl (n_line_counter) :=
oe_blanket_pub.g_miss_blanket_line_rec;
l_line_tbl (n_line_counter).operation := oe_globals.g_opr_create;
l_line_tbl (n_line_counter).sold_to_org_id := l_sold_to_org_id;
l_line_tbl (n_line_counter).inventory_item_id := l_inv_item_id;
l_line_tbl (n_line_counter).ship_from_org_id :=
l_line_ship_from_org_id;
l_line_tbl (n_line_counter).invoice_to_org_id :=
l_line_inv_to_org_id;
l_line_tbl (n_line_counter).ship_to_org_id := l_line_ship_to_orgid;
l_line_tbl (n_line_counter).item_identifier_type := 'INT';
l_line_tbl (n_line_counter).order_quantity_uom := l_uom;
l_line_tbl (n_line_counter).CONTEXT := 'XXXX';
l_line_tbl (n_line_counter).start_date_active :=
lc_rec_lines.line_activ_date;
l_line_tbl (n_line_counter).end_date_active :=
lc_rec_lines.line_exp_date;
-- l_line_tbl (n_line_counter).accounting_rule_id := l_acct_rule_id;
-- l_line_tbl (n_line_counter).invoicing_rule_id := l_invo_rule_id;
l_line_tbl (n_line_counter).attribute1 := lc_rec_lines.pue;
l_line_tbl (n_line_counter).attribute2 :=
TO_CHAR (lc_rec_lines.start_date, 'YYYY/MM/DD HH24:MI:SS');
l_line_tbl (n_line_counter).attribute3 :=
TO_CHAR (lc_rec_lines.termination_date,
'YYYY/MM/DD HH24:MI:SS');
l_line_tbl (n_line_counter).attribute4 := lc_rec_lines.dsp_flag;
l_line_tbl (n_line_counter).attribute5 := lc_rec_lines.quantity;
l_line_tbl (n_line_counter).attribute6 := TO_NUMBER(lc_rec_lines.price,'99999999999.999999');
l_line_tbl (n_line_counter).attribute7 :=
lc_rec_lines.min_order_qty;
l_line_tbl (n_line_counter).attribute8 :=
lc_rec_lines.product_code;
l_line_tbl (n_line_counter).attribute9 :=
lc_rec_lines.account_rule;
l_line_val_tbl (n_line_counter) := l_line_val_rec;
END LOOP;

fnd_file.put_line (fnd_file.LOG,
'l_line_tbl Count is --> ' || l_line_tbl.COUNT
);
--DBMS_OUTPUT.put_line ('Before calling Process Blanket API');
oe_msg_pub.initialize;
fnd_file.put_line (fnd_file.LOG,
'Line Counter is --> ' || n_line_counter
);

IF n_line_counter > 0
THEN
fnd_file.put_line
(fnd_file.LOG,
'-----------------------API Calling-------------------------'
);

BEGIN
oe_blanket_pub.process_blanket
(p_org_id => 82,
p_operating_unit => NULL,
p_api_version_number => p_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_header_rec => l_hdr_rec,
p_header_val_rec => l_hdr_val_rec,
p_line_tbl => l_line_tbl,
p_line_val_tbl => l_line_val_tbl,
p_control_rec => l_control_rec,
x_header_rec => x_header_rec,
x_line_tbl => x_line_tbl
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'API Error :' || SQLERRM);
END;
END IF;

fnd_file.put_line (fnd_file.LOG, 'API Completed -> ');

IF NVL (x_return_status, '~') <> 'S'
THEN
fnd_file.put_line (fnd_file.LOG,
'Return status --> ' || x_return_status
);
FOR lc_err IN 1 .. oe_msg_pub.count_msg
LOOP
l_msg :=
oe_msg_pub.get (p_msg_index => lc_err,
p_encoded => fnd_api.g_false
);
fnd_file.put_line (fnd_file.LOG,
'Error Message in Loop--> ' || l_msg
);
END LOOP;
BEGIN
UPDATE xx_om_bsa_stg
SET error_message = l_msg,
process_flag = x_return_status
WHERE process_flag = 'V'
AND source_contract_no = lc_rec.source_contract_no;
END;
END IF;

IF NVL (x_return_status, '~') = 'S'
THEN
BEGIN
UPDATE xx_om_bsa_stg
SET order_number = x_header_rec.order_number,
process_flag = x_return_status
WHERE process_flag = 'V'
AND source_contract_no = lc_rec.source_contract_no;
END;

fnd_file.put_line (fnd_file.LOG,
'Header ID :' || x_header_rec.header_id
);
fnd_file.put_line (fnd_file.LOG,
'Order number :' || x_header_rec.order_number
);
END IF;

COMMIT;
fnd_file.put_line (fnd_file.LOG,
'Exit In Header Loop for ->'
|| lc_rec.source_contract_no
);
END LOOP;
END load_bsa;

-------------------------------------------
-- Procedure to do the total Validation
-------------------------------------------
PROCEDURE prevalidation
IS
lc_verify_flag VARCHAR2 (1) := NULL;
l_error_message VARCHAR2 (4000) := NULL;
l_cnt NUMBER := 0;
ln_cust_acct_id NUMBER := 0;
ln_party_id NUMBER := 0;
lc_item_cnt VARCHAR2 (20) := NULL;
l_ship_from_orgid NUMBER := 0;
l_inv_to_orgid NUMBER := 0;
l_ship_to_orgid NUMBER := 0;
l_dspflag VARCHAR2(20) := NULL;
l_inv_rule_id NUMBER := 0;
l_acc_rule_id NUMBER := 0;
l_mth_processed VARCHAR2(20) := NULL;
l_dc VARCHAR2(100) := NULL;
l_sl VARCHAR2(200) := NULL;
l_bl VARCHAR2(200) := NULL;
l_hdr_cnt NUMBER := 0;

CURSOR lcu_bsa_stg
IS
SELECT customer_number, customer_po, commencement_date,
expiration_date, month_processed, contract_end_date,
ship_to_location, data_centre, bill_to_location, item,
line_number, line_activ_date, line_exp_date, line_shipto,
line_data_centre, line_bill_to_loc, pue, product_code,
start_date, termination_date, dsp_flag, quantity, price,
min_order_qty, account_rule, invoicing_rule, record_id,
source_contract_no
FROM xx_om_bsa_stg
WHERE process_flag = 'N'
ORDER BY source_contract_no;-- record_id;
BEGIN
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
fnd_file.put_line
(fnd_file.LOG,
'--------------- Entering into Prevalidation -----------'
);

---------------------------
--Validating the Customer details
---------------------------
FOR lc_rec IN lcu_bsa_stg
LOOP
lc_verify_flag := 'Y';
l_error_message := NULL;

BEGIN
SELECT cust_account_id, party_id
INTO ln_cust_acct_id, ln_party_id
FROM hz_cust_accounts
WHERE account_number = lc_rec.customer_number AND status = 'A';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_cust_acct_id := 0;
l_error_message :=
l_error_message
|| ' ~ Customer is not exist '
|| '~'
|| SQLERRM;
lc_verify_flag := 'N';
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Customer error' || '~' || SQLERRM;
END;

----------------------------------------
-- Data centre count validation
---------------------------------------

BEGIN
l_dc := NULL;
select distinct data_centre into l_dc
FROM xx_om_bsa_stg
WHERE process_flag = 'N'
AND customer_number = lc_rec.customer_number
AND source_contract_no = lc_rec.source_contract_no;

EXCEPTION WHEN TOO_MANY_ROWS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Multiple header level data centre for same customer ' || '~' || lc_rec.customer_number;
WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ data centre validation error ' || '~' || lc_rec.customer_number|| ' ~ '||SQLERRM;

END;

----------------------------------------
-- ship to location count validation
---------------------------------------

BEGIN
l_sl := NULL;
select distinct ship_to_location into l_sl
FROM xx_om_bsa_stg
WHERE process_flag = 'N'
AND customer_number = lc_rec.customer_number
AND source_contract_no = lc_rec.source_contract_no;

EXCEPTION WHEN TOO_MANY_ROWS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Multiple header level ship to location for same customer ' || '~' || lc_rec.customer_number;
WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ ship to location validation error ' || '~' || lc_rec.customer_number|| ' ~ '||SQLERRM;
END;

----------------------------------------
-- bill to location count validation
---------------------------------------

BEGIN
l_bl := NULL;
select distinct bill_to_location into l_bl
FROM xx_om_bsa_stg
WHERE process_flag = 'N'
AND customer_number = lc_rec.customer_number
AND source_contract_no = lc_rec.source_contract_no;

EXCEPTION WHEN TOO_MANY_ROWS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Multiple header level bill to location for same customer ' || '~' || lc_rec.customer_number;
WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ bill to location validation error ' || '~' || lc_rec.customer_number|| ' ~ '||SQLERRM;
END;

----------------------------------------
-- Header data count validation
---------------------------------------

BEGIN
l_hdr_cnt := 0;
SELECT COUNT(*) INTO l_hdr_cnt
FROM (
SELECT DISTINCT customer_number, customer_po, commencement_date,
expiration_date, month_processed, contract_end_date,
ship_to_location, data_centre, bill_to_location,
source_contract_no
FROM xx_om_bsa_stg
WHERE process_flag = 'N'
AND customer_number = lc_rec.customer_number
AND source_contract_no = lc_rec.source_contract_no);
IF l_hdr_cnt > 1 THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Multiple header level data for same customer ' || '~' || lc_rec.customer_number;
END IF;
EXCEPTION WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ header level data validation error ' || '~' || lc_rec.customer_number|| ' ~ '||SQLERRM;
END;

-----------------------------
--Validating the Activation Date
-----------------------------
IF lc_rec.commencement_date IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' Activation date is null - '
|| '-'
|| lc_rec.customer_number;
fnd_file.put_line (fnd_file.LOG,
' Activation date is null - '
|| '-'
|| lc_rec.customer_number
);
END IF;

---------------------------------
--Validating the Month Processed
---------------------------------
IF lc_rec.month_processed IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Month Processed value is null - '
|| '-'
|| lc_rec.customer_number;
fnd_file.put_line (fnd_file.LOG,
' ~ Month Processed value is null - '
|| '-'
|| lc_rec.customer_number
);
END IF;

---------------------------------
--Validating the Month Processed
---------------------------------
BEGIN

SELECT to_char(to_date(lc_rec.MONTH_PROCESSED,'MON-YY'),'MON-YY')
INTO l_mth_processed
FROM DUAL;
EXCEPTION WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Month Processed value is Invalid - '
|| '-'
|| lc_rec.customer_number
|| '-'
||lc_rec.MONTH_PROCESSED;
fnd_file.put_line (fnd_file.LOG,
' ~ Month Processed value is Invalid - '
|| '-'
|| lc_rec.customer_number
|| '-'
||lc_rec.MONTH_PROCESSED
);
END;

-----------------------------------
--Validating the Contract End Date
-----------------------------------
IF lc_rec.contract_end_date IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Contract End Date is null - '
|| '-'
|| lc_rec.customer_number;
fnd_file.put_line (fnd_file.LOG,
' ~ Contract End Date is null - '
|| '-'
|| lc_rec.customer_number
);
END IF;

-----------------------------------
--Validating the Ship to
-----------------------------------
IF lc_rec.ship_to_location IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Ship To Location is null - '
|| '-'
|| lc_rec.customer_number;
fnd_file.put_line (fnd_file.LOG,
' ~ Ship To Location is null - '
|| '-'
|| lc_rec.customer_number
);
END IF;

-----------------------------------
--Validating the Bill to
-----------------------------------
IF lc_rec.bill_to_location IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Bill To Location is null - '
|| '-'
|| lc_rec.customer_number;
fnd_file.put_line (fnd_file.LOG,
' ~ Bill To Location is null - '
|| '-'
|| lc_rec.customer_number
);
END IF;

------------------------------------
-- Inventory Item Validation
------------------------------------
BEGIN
SELECT DISTINCT '1'
INTO lc_item_cnt
FROM mtl_system_items_b msib,
org_organization_definitions ood
WHERE msib.organization_id = ood.organization_id
AND segment1 = lc_rec.item
AND ood.operating_unit = 82
AND ood.ORGANIZATION_CODE = lc_rec.data_centre
AND msib.enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Inventory item is not available ';
END;

-----------------------------
--Validating the Activation date and expiration date
-----------------------------
/* IF lc_rec.line_activ_date IS NOT NULL
AND lc_rec.line_exp_date IS NOT NULL
THEN
IF TO_DATE (lc_rec.line_activ_date, 'DD-MON-YYYY') >
TO_DATE (lc_rec.line_exp_date, 'DD-MON-YYYY')
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Expiration date should be greater than Activation date ';
END IF;
END IF;

*/
-----------------------------
--Validating the expiration date
-----------------------------
IF lc_rec.line_exp_date IS NOT NULL
THEN
IF TO_DATE (lc_rec.line_exp_date, 'DD-MON-YYYY') > trunc(sysdate)
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Expiration date should be greater than current date ';
END IF;
END IF;

-----------------------------
--Validating the Start Date
-----------------------------
IF lc_rec.start_date IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Start Date is mandatory';
END IF;

-----------------------------
--Validating the Start Date and Termination date
-----------------------------
IF lc_rec.start_date IS NOT NULL
AND lc_rec.termination_date IS NOT NULL
THEN
IF TO_DATE (lc_rec.start_date, 'DD-MON-YYYY') >
TO_DATE (lc_rec.termination_date, 'DD-MON-YYYY')
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Start date should be lesser than termination date';
END IF;
END IF;

--------------------------------------------
--Validating for PUE to accept only numeric
---------------------------------------------
IF lc_rec.pue IS NOT NULL
THEN
IF NVL (LENGTH (TRIM (TRANSLATE (lc_rec.pue, ' +-.0123456789',
' ')
)
),
0
) > 0
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ PUE value should be in numeric ';
END IF;
END IF;

--------------------------------------------
-- Validation for ware house / data centre
--------------------------------------------
IF lc_rec.data_centre IS NOT NULL
THEN
BEGIN
SELECT organization_id
INTO l_ship_from_orgid
FROM org_organization_definitions
WHERE organization_code = lc_rec.data_centre;
EXCEPTION
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ data center is invalid ';
fnd_file.put_line (fnd_file.LOG,
'data centre --> ' || lc_rec.data_centre
);
END;
END IF;

--------------------------------------------
-- Validation for Dynamic Static Flag
--------------------------------------------
IF lc_rec.dsp_flag IS NULL
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Dynamic Static Pro service Flag is null ';
fnd_file.put_line
(fnd_file.LOG,
'Dynamic Static Pro service Flag is null --> '
|| lc_rec.dsp_flag
);
END IF;

--------------------------------------------
-- Validation for Dynamic Static Flag
----------------------------------------------

IF lc_rec.dsp_flag IS NOT NULL THEN

BEGIN
select CATEGORY_CONCAT_SEGS
into l_dspflag
from MTL_ITEM_CATEGORIES_V micv, mtl_system_items_b msib,
org_organization_definitions ood
WHERE micv.inventory_item_id = msib.inventory_item_id
and msib.organization_id = micv.organization_id
and msib.organization_id = ood.organization_id
and ood.ORGANIZATION_CODE = lc_rec.data_centre
and micv.CATEGORY_SET_NAME = 'Type Category Set'
and msib.SEGMENT1 = lc_rec.item;

EXCEPTION WHEN OTHERS THEN
l_dspflag := '~';
END;
IF lc_rec.dsp_flag <> l_dspflag
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Dynamic Static Pro service Flag is Invalid ';
fnd_file.put_line
(fnd_file.LOG,
'Dynamic Static Pro service Flag is Invalid --> '
|| lc_rec.dsp_flag||' ~ '||lc_rec.item
);
END IF;
END IF;

--------------------------------------------
-- Validation for Quantity
----------------------------------------------

/*IF lc_rec.dsp_flag = 'D' AND lc_rec.quantity > 0 THEN

lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ For Dynamic, Quantity should be null ';
fnd_file.put_line
(fnd_file.LOG,
'For Dynamic, Quantity should be null --> '
|| lc_rec.dsp_flag||' ~ '||lc_rec.quantity||' ~ '||lc_rec.item
);
END IF;*/

-------------------------------------------------------------------
-- Validation for line Item level active date and expiration date
-------------------------------------------------------------------

IF validate_line_dates(lc_rec.item,lc_rec.source_contract_no) = 'INVALID' THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Line Item level activation and expiration date is not in order ';
fnd_file.put_line
(fnd_file.LOG,
'Line Item level activation and expiration date is not in order --> '
|| lc_rec.customer_number||' ~ '||lc_rec.item
);
END IF;

------------------------------------------------
-- Validation for Price and minimum order qty
------------------------------------------------

/*IF lc_rec.price is not null AND lc_rec.min_order_qty IS NOT NULL THEN

lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Value should not given for Both price and minimum order qty ';
fnd_file.put_line
(fnd_file.LOG,
'Value should not given for Both price and minimum order qty --> '
|| lc_rec.customer_number||' ~ '||lc_rec.min_order_qty||' ~ '||lc_rec.price
);
END IF;*/

-------------------------------------
-- Validation for Invoicing rules
-------------------------------------
/*
IF lc_rec.invoicing_rule IS NOT NULL THEN
BEGIN
SELECT rule_id
INTO l_inv_rule_id
FROM ra_rules
WHERE type = 'I'
AND NAME = lc_rec.invoicing_rule;
EXCEPTION WHEN OTHERS THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Invoicing rule name is invalid ';
fnd_file.put_line
(fnd_file.LOG,
'Invoicing rule name is invalid --> '
|| lc_rec.customer_number||' ~ '||lc_rec.invoicing_rule
);
END;
END IF;
*/
-------------------------------------
-- Validation for Accounting rules
-------------------------------------
IF lc_rec.account_rule IS NULL THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message
|| ' ~ Accounting rule is null ';
fnd_file.put_line
(fnd_file.LOG,
'Accounting rule is null --> '
|| lc_rec.customer_number||' ~ '||lc_rec.account_rule
);
END IF;

--------------------------------------------
-- Validation for Bill to Location
--------------------------------------------
IF lc_rec.bill_to_location IS NOT NULL
THEN
BEGIN
SELECT csu.site_use_id
INTO l_inv_to_orgid
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec.bill_to_location
AND csu.site_use_code = 'BILL_TO';
EXCEPTION
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Bill to location is invalid. ';
fnd_file.put_line (fnd_file.LOG,
'Bill to Location --> '
|| lc_rec.bill_to_location
);
END;
END IF;

--------------------------------------------
-- Validation for ship to Location
--------------------------------------------
IF lc_rec.ship_to_location IS NOT NULL
THEN
BEGIN
SELECT csu.site_use_id
INTO l_ship_to_orgid
FROM apps.hz_cust_accounts_all cus,
apps.hz_cust_acct_sites_all cussite,
apps.hz_cust_site_uses_all csu
WHERE cus.cust_account_id = cussite.cust_account_id
AND csu.cust_acct_site_id = cussite.cust_acct_site_id
AND account_number = lc_rec.customer_number
AND csu.LOCATION = lc_rec.ship_to_location
AND csu.site_use_code = 'SHIP_TO';
EXCEPTION
WHEN OTHERS
THEN
lc_verify_flag := 'N';
l_error_message :=
l_error_message || ' ~ Ship to location is invalid. ';
fnd_file.put_line (fnd_file.LOG,
'Ship to Location --> '
|| lc_rec.ship_to_location
);
END;
END IF;

IF lc_verify_flag = 'N'
THEN
UPDATE xx_om_bsa_stg
SET error_message = l_error_message,
process_flag = 'VE' -- Validation Error
WHERE record_id = lc_rec.record_id
AND process_flag = 'N'
AND request_id = gn_conc_req_id
AND source_contract_no = lc_rec.source_contract_no;

fnd_file.put_line (fnd_file.LOG,
' Validation error records --> '
|| '~'
|| lc_rec.customer_number
);
ELSE
UPDATE xx_om_bsa_stg
SET error_message = l_error_message,
process_flag = 'V' -- Validated
WHERE record_id = lc_rec.record_id
AND process_flag = 'N'
AND request_id = gn_conc_req_id
AND source_contract_no = lc_rec.source_contract_no;

fnd_file.put_line
(fnd_file.LOG,
'Validation Success records customer number --> '
|| lc_rec.customer_number
);
END IF;
END LOOP;

fnd_file.put_line (fnd_file.LOG,
'--------------- Prevalidation ended -----------'
);
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);

-------------------------------------------------
-- Updating the Dependent Supplier Records
-- if any 1 record has Validation error Mark all
-- depedent records as Error
-------------------------------------------------
UPDATE xx_om_bsa_stg stg
SET error_message =
'DEPENDENCY ERROR: Blanket Sales Agreement Validations Failed ',
process_flag = 'VE' -- Validation Error
WHERE NVL (process_flag, 'X') <> 'VE'
AND request_id = gn_conc_req_id
AND EXISTS (
SELECT '1'
FROM xx_om_bsa_stg stg1
WHERE 1 = 1
AND stg1.request_id = stg.request_id
AND NVL (stg1.process_flag, 'X') = 'VE'
AND stg.source_contract_no = stg1.source_contract_no);

COMMIT;
END prevalidation;

PROCEDURE trim_data
IS
BEGIN
--------------------------------------
-- Update to remove unwanted spaces
-------------------------------------
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
fnd_file.put_line (fnd_file.LOG,
'--------------- Entering Trim Data-----------'
);

UPDATE xx_om_bsa_stg
SET customer_number = LTRIM (RTRIM (customer_number)),
customer_po = LTRIM (RTRIM (customer_po)),
commencement_date = LTRIM (RTRIM (commencement_date)),
expiration_date = LTRIM (RTRIM (expiration_date)),
month_processed = UPPER(LTRIM (RTRIM (month_processed))),
contract_end_date = LTRIM (RTRIM (contract_end_date)),
ship_to_location = LTRIM (RTRIM (ship_to_location)),
data_centre = LTRIM (RTRIM (data_centre)),
bill_to_location = LTRIM (RTRIM (bill_to_location)),
item = LTRIM (RTRIM (item)),
line_number = LTRIM (RTRIM (line_number)),
line_activ_date = LTRIM (RTRIM (line_activ_date)),
line_exp_date = LTRIM (RTRIM (line_exp_date)),
line_shipto = LTRIM (RTRIM (line_shipto)),
line_data_centre = LTRIM (RTRIM (line_data_centre)),
line_bill_to_loc = LTRIM (RTRIM (line_bill_to_loc)),
pue = LTRIM (RTRIM (pue)),
product_code = LTRIM (RTRIM (product_code)),
start_date = LTRIM (RTRIM (start_date)),
termination_date = LTRIM (RTRIM (termination_date)),
dsp_flag = LTRIM (RTRIM (dsp_flag)),
quantity = LTRIM (RTRIM (quantity)),
price = LTRIM (RTRIM (replace(price,'-',NULL))),
min_order_qty = LTRIM (RTRIM (min_order_qty)),
account_rule = LTRIM (RTRIM (account_rule)),
invoicing_rule = ltrim(rtrim(replace(INVOICING_RULE,chr(13),''))),
request_id = gn_conc_req_id,
created_by = gn_user_id,
last_updated_by = gn_user_id
WHERE NVL (process_flag, 'N') = 'N';

fnd_file.put_line (fnd_file.LOG,
'--------------- Trim Data Completed -----------'
);
fnd_file.put_line
(fnd_file.LOG,
'###################################################################################'
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error Updating the NEW RECORDS - TRIM DATA : '
|| SQLCODE
|| ' - '
|| SQLERRM
);
END trim_data;

PROCEDURE record_status
AS

l_total_cnt NUMBER;
l_bsa_success_cnt NUMBER;
l_bsa_error_cnt NUMBER;
l_order_num VARCHAR2(20);
lc_hdr_message VARCHAR2(255) := NULL;
lc_err_cnt NUMBER := 0;

CURSOR lcu_err_rec
IS
SELECT DISTINCT source_contract_no,customer_number, error_message, request_id
FROM xx_om_bsa_stg_a
WHERE request_id = gn_conc_req_id
AND PROCESS_FLAG LIKE '%E%';

CURSOR lcu_succ_rec
IS
SELECT DISTINCT REQUEST_ID, RPAD(Substr(source_contract_no,1,38),40)||
RPAD(Substr(customer_number,1,38),40)||
RPAD(Substr(order_number,1,38),40)||
RPAD(Decode(NVL(PROCESS_FLAG,'X'),'S','Success ','NA','NA','No'),8) Succ_Message
FROM xx_om_bsa_stg_a
WHERE request_id = gn_conc_req_id
AND PROCESS_FLAG in ('S','IE');

BEGIN
-------------------------------------------------------------
-- UPdating All the Success Records with Process Flag = 'S'
-------------------------------------------------------------

UPDATE xx_om_bsa_stg_a
SET PROCESS_FLAG = 'S'
WHERE REQUEST_ID = GN_CONC_REQ_ID
AND PROCESS_FLAG like 'S%';

COMMIT;

SELECT COUNT(DISTINCT customer_number)
INTO l_total_cnt
FROM xx_om_bsa_stg_a
WHERE request_id = gn_conc_req_id;

SELECT COUNT(DISTINCT customer_number)
INTO l_bsa_success_cnt
FROM xx_om_bsa_stg_a
WHERE request_id = gn_conc_req_id
AND process_flag = 'S';

SELECT COUNT(DISTINCT customer_number)
INTO l_bsa_error_cnt
FROM xx_om_bsa_stg_a
WHERE request_id = gn_conc_req_id
AND process_flag LIKE '%E%';

fnd_file.put_line(fnd_file.output,'Total Number of records ' || l_total_cnt);
fnd_file.put_line(fnd_file.output,'====================================================================');
fnd_file.put_line(fnd_file.output,'================ Success Records ===================================');
fnd_file.put_line(fnd_file.output,'====================================================================');
fnd_file.put_line(fnd_file.output,'Total Number of BSA success records ' || l_bsa_success_cnt);
fnd_file.put_line(fnd_file.output,'====================================================================');
fnd_file.put_line(fnd_file.output,'================ Error Records ===================================');
fnd_file.put_line(fnd_file.output,'====================================================================');
fnd_file.put_line(fnd_file.output,'Total Number of BSA error records ' || l_bsa_error_cnt);
fnd_file.put_line(fnd_file.output,'====================================================================');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'================ Error output =======================================');
fnd_file.put_line(fnd_file.output,'====================================================================');

-----------------------------
-- Printing Error Records
-----------------------------
lc_err_cnt := 0;

FOR lcu_rec IN lcu_err_rec
LOOP
lc_err_cnt := lc_err_cnt + 1;
IF lc_err_cnt = 1
THEN
fnd_file.put_line(fnd_file.output,' source_contract_no ' || ' customer_number ' ||' Error Message ');
fnd_file.put_line(fnd_file.output,'====================================================================');
END IF;
fnd_file.put_line(fnd_file.output,lcu_rec.source_contract_no||' ' || SUBSTR(lcu_rec.customer_number,1,18)||' ' ||Substr(lcu_rec.error_message,1,200) );
END LOOP;

lc_hdr_message := RPAD('Source Contract Number',40)||RPAD(' Customer Number ',25)||
RPAD(' Order Number ',25);

-----------------------------
-- Printing Success Records
-----------------------------
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'.........Printing Success / Partial Success Records........');
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,lc_hdr_message);
fnd_file.put_line(fnd_file.output,RPAD(' ',200,'=') );
-- fnd_file.put_line(fnd_file.output,'========================================================================================================');

FOR lcu_succ in lcu_succ_rec
LOOP
fnd_file.put_line(fnd_file.output,lcu_succ.Succ_message);
END LOOP;
fnd_file.put_line(fnd_file.output,RPAD(' ',200,'=') );
-- fnd_file.put_line(fnd_file.output,'========================================================================================================');
END;

FUNCTION validate_line_dates(l_item VARCHAR2,l_source_contract VARCHAR2 ) RETURN VARCHAR2
IS
l_flag NUMBER := 0;
BEGIN
select NVL(SUM(DISTINCT fla),0) into l_flag from (
select T.*,CASE WHEN ran = 1 AND LINE_ACTIV_DATE <= NVL(LINE_EXP_DATE,SYSDATE) THEN 1
WHEN RAN >1 AND LINE_ACTIV_DATE <= NVL(LINE_EXP_DATE,SYSDATE) AND LINE_ACTIV_DATE > LAG_EXP_DATE AND NVL(LINE_EXP_DATE,LINE_ACTIV_DATE+1) > NVL(lag_ac_date,SYSDATE) THEN 1
ELSE 2 END AS FLA from (
select item,LINE_ACTIV_DATE,LINE_EXP_DATE,lead(item,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_item,
lag(item,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_item,
lead(LINE_ACTIV_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_ac_date,
lag(LINE_ACTIV_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_ac_date,
lead(LINE_EXP_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lead_exp_date,
lag(LINE_EXP_DATE,1) OVER (ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) lag_exp_date,
ROW_NUMBER() OVER (PARTITION BY item ORDER BY item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))) ran
from xx_om_bsa_stg
where (SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item) in (
select SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item from xx_om_bsa_stg
WHERE ITEM = l_item
and SOURCE_CONTRACT_NO=l_source_contract
group by SOURCE_CONTRACT_NO, CUSTOMER_NUMBER,item
having count(*) > 1)
order by item,LINE_ACTIV_DATE,NVL(LINE_EXP_DATE,TRUNC(SYSDATE))
) T);
IF l_flag <= 1 THEN
RETURN 'VALID';
ELSE
RETURN 'INVALID';
END IF;
END;
END XX_BSA_LOAD_PKG;
/

Wednesday 26 September 2018

Oracle Blanket Sales Agreement Loader API

No comments:

Post a Comment