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;
/
