po 3 way match

CREATE OR REPLACE PACKAGE APPS.xxhbc_3way_po_match_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_3way_po_match_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Shekhar Nema */
/* */
/* DATE : 23-DEC-2009 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will be used for creation standard purchase /*
/* order from retak system /*
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 23/12/09 Shekhar Nema 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
PROCEDURE get_retek_records (errbuf OUT VARCHAR2, retcode OUT VARCHAR2);
PROCEDURE process_existing_lines (p_po_no VARCHAR2);
FUNCTION get_agent_id (p_buyer_user_id VARCHAR2)
RETURN NUMBER;
FUNCTION check_existing_lines (p_po_num VARCHAR2)
RETURN NUMBER;
FUNCTION check_shipment_lines (p_po_no VARCHAR2, p_line_num VARCHAR2)
RETURN NUMBER;
PROCEDURE get_vendor_id (
p_vendor_site_code IN VARCHAR2
,p_vendor_id OUT NUMBER
,p_vendor_site_id OUT NUMBER
,p_term_id OUT NUMBER
);
PROCEDURE get_goods_receive_date (
p_po_no VARCHAR2
,p_line_no VARCHAR2
,p_shipment_no VARCHAR2
,p_goods_received_date VARCHAR2
);
FUNCTION get_term_id (p_po_number VARCHAR2)
RETURN NUMBER;
FUNCTION get_cc_id (p_po_number VARCHAR2, p_line_num VARCHAR2, p_shipment_num VARCHAR2)
RETURN NUMBER;
END xxhbc_3way_po_match_pkg;
/

CREATE OR REPLACE PACKAGE BODY APPS.xxhbc_3way_po_match_pkg
AS
/* -------------------------------------------------------------------------- */
/* Program Name : xxcofi_3way_po_match_pkg */
/* */
/* TYPE : PL/SQL Package */
/* */
/* Input Parms : */
/* */
/* Output Parms : -- */
/* */
/* Table Access : -- */
/* */
/* AUTHOR : Shekhar Nema */
/* */
/* DATE : 23-DEC-2009 */
/* */
/* VERSION : 1.0 */
/* */
/* DESCRIPTION : This package will be used for creation standard purchase /*
/* order from retak system /*
/* */
/* */
/* */
/* CHANGE HISTORY */
/* -------------------------------------------------------------------------- */
/* DATE AUTHOR VERSION REASON */
/* -------------------------------------------------------------------------- */
/* 23/12/09 Shekhar Nema 1.0 Initial creation */
/* -------------------------------------------------------------------------- */
FUNCTION check_existing_lines (p_po_num VARCHAR2)
RETURN NUMBER
/* --------Function to check the po lines existing in database ----------*/
IS
v_header_id NUMBER := 0;
v_po_check NUMBER := 0;
BEGIN
BEGIN
SELECT po_header_id
INTO v_header_id
FROM po_headers_all
WHERE segment1 = p_po_num;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_header_id := 0;
END;
SELECT COUNT (*)
INTO v_po_check
FROM po_lines_all
WHERE po_header_id = v_header_id;
IF v_po_check = 0
THEN
RETURN (0);
ELSE
RETURN (1);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'in'
|| ''
|| 'check_existing_lines function for po#'
|| ' '
|| p_po_num
);
END;
FUNCTION check_shipment_lines (p_po_no VARCHAR2, p_line_num VARCHAR2)
RETURN NUMBER
/* --------Function to check the po lines existing in database ----------*/
IS
v_line_check NUMBER := 0;
BEGIN
BEGIN
SELECT COUNT (*)
INTO v_line_check
FROM po_headers_all pha, po_lines_all pla
WHERE pha.po_header_id = pla.po_header_id
AND pha.segment1 = p_po_no
AND pla.line_num = p_line_num;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_line_check := 0;
END;
IF v_line_check = 0
THEN
RETURN (0);
ELSE
RETURN (1);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'in'
|| ' '
|| 'check_existing_line shipment function for po#'
|| ' '
|| p_po_no
);
END;
FUNCTION get_agent_id (p_buyer_user_id VARCHAR2)
RETURN NUMBER
/* --------Function to get the agent_id----------*/
IS
v_func_agent_id NUMBER := 0;
BEGIN
BEGIN
SELECT user_id
INTO v_func_agent_id
FROM fnd_user
WHERE user_name = p_buyer_user_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_agent_id := 0;
fnd_file.put_line (fnd_file.LOG
, 'agent_id is not existing in oracle for buyer_user_id'
|| ' '
|| p_buyer_user_id
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'for' || '' || 'agent_id'
);
END;
RETURN (v_func_agent_id);
END;
FUNCTION get_term_id (p_po_number VARCHAR2)
RETURN NUMBER
IS
/* --------Function to get the term_id----------*/
v_func_term_id ap_terms.term_id%TYPE := 0;
v_func_comb_term_id ap_terms.term_id%TYPE := 0;
v_func_attribute1 ap_terms_lines.attribute1%TYPE := NULL;
v_func_term_due_days NUMBER := 0;
v_func_term_discount_percent NUMBER := 0;
v_func_term_discount_days NUMBER := 0;
v_func_term_dis_day_of_the_mon NUMBER := 0;
v_func_term_due_percent NUMBER := 0;
v_func_term_dis_months_frd NUMBER := 0;
v_func_term_process NUMBER := 0;
v_func_term_comb_process NUMBER := 0;
BEGIN
BEGIN
SELECT DISTINCT term_due_days
,term_discount_percent
,term_discount_days
,term_discount_day_of_the_month
,term_due_percent
,term_discount_months_forward
INTO v_func_term_due_days
,v_func_term_discount_percent
,v_func_term_discount_days
,v_func_term_dis_day_of_the_mon
,v_func_term_due_percent
,v_func_term_dis_months_frd
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_po_number;
v_func_term_comb_process := 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_term_comb_process := 2;
fnd_file.put_line
(fnd_file.LOG
, 'Term existing in oracle for the combination of term_due_days,term_discount_percent,term_discount_days,term_discount_day_of_the_month,term_due_percent,term_discount_months_forward for the PO#'
|| ' '
|| p_po_number
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'Calculation of term_id from the Combination'
|| 'for po#'
|| ' '
|| p_po_number
);
END;
BEGIN
SELECT DISTINCT term_id
INTO v_func_comb_term_id
FROM ap_terms_lines
WHERE NVL (due_days, 999) = NVL (v_func_term_due_days, 999)
AND NVL (discount_percent, 999) = NVL (v_func_term_discount_percent, 999)
AND NVL (discount_days, 999) = NVL (v_func_term_discount_days, 999)
AND NVL (discount_day_of_month, 999) =
NVL (v_func_term_dis_day_of_the_mon, 999)
AND NVL (due_percent, 999) = NVL (v_func_term_due_percent, 999)
AND NVL (discount_months_forward, 999) = NVL (v_func_term_dis_months_frd, 999);
v_func_term_comb_process := 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_term_comb_process := 2;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'for' || '' || 'term_id'
);
END;
BEGIN
SELECT DISTINCT term_identifier
INTO v_func_attribute1
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_po_number;
v_func_term_process := 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_term_process := 2;
fnd_file.put_line (fnd_file.LOG
, 'Term_id is not existing in oracle for the term_identifier='
|| ' '
|| v_func_attribute1
);
END;
BEGIN
SELECT DISTINCT term_id
INTO v_func_term_id
FROM ap_terms
WHERE attribute1 = v_func_attribute1;
v_func_term_process := 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_term_process := 2;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'for' || ''
|| 'term_identifier'
);
END;
IF (v_func_term_comb_process = 1 AND v_func_term_process = 2)
THEN
v_func_term_id := v_func_comb_term_id;
fnd_file.put_line (fnd_file.LOG
, 'Term_id'
|| ' '
|| v_func_comb_term_id
|| ' '
|| 'is calculated from term parameters'
|| 'for po#'
|| ' '
|| p_po_number
);
ELSIF (v_func_term_comb_process = 2 AND v_func_term_process = 1)
THEN
v_func_term_id := v_func_term_id;
fnd_file.put_line (fnd_file.LOG
, 'Term_id'
|| ' '
|| v_func_term_id
|| ' '
|| 'is calculated from term identifier'
|| ' '
|| v_func_attribute1
|| ' '
|| 'for po#'
|| ' '
|| p_po_number
);
ELSIF (v_func_term_comb_process = 1 AND v_func_term_process = 1)
THEN
v_func_term_id := v_func_term_id;
fnd_file.put_line (fnd_file.LOG
, 'Term_id'
|| ' '
|| v_func_term_id
|| ' '
|| 'is calculated from term identifier'
|| ' '
|| v_func_attribute1
|| ' '
|| 'for po#'
|| ' '
|| p_po_number
);
ELSIF (v_func_term_comb_process = 2 AND v_func_term_process = 2)
THEN
v_func_term_id := 0;
END IF;
RETURN (v_func_term_id);
END;
FUNCTION get_cc_id (p_po_number VARCHAR2, p_line_num VARCHAR2, p_shipment_num VARCHAR2)
RETURN NUMBER
IS
/* --------Function to get the code_combination_id----------*/
v_func_gl_accrual_segment1 gl_code_combinations.segment1%TYPE;
v_func_gl_accrual_segment2 gl_code_combinations.segment2%TYPE;
v_func_gl_accrual_segment4 gl_code_combinations.segment4%TYPE;
v_func_gl_accrual_segment5 gl_code_combinations.segment5%TYPE;
v_func_code_combination_id NUMBER := 0;
BEGIN
BEGIN
SELECT gl_accrual_segment1
,gl_accrual_segment2
,gl_accrual_segment4
,gl_accrual_segment5
INTO v_func_gl_accrual_segment1
,v_func_gl_accrual_segment2
,v_func_gl_accrual_segment4
,v_func_gl_accrual_segment5
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_po_number
AND (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) = p_line_num
AND shipment_num = p_shipment_num;
SELECT code_combination_id
INTO v_func_code_combination_id
FROM gl_code_combinations
WHERE segment1 = v_func_gl_accrual_segment1
AND segment2 = v_func_gl_accrual_segment2
AND segment3 = '0000'
AND segment4 = v_func_gl_accrual_segment4
AND segment5 = v_func_gl_accrual_segment5
AND segment6 = '000000';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_func_code_combination_id := 88663;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'for' || '' || 'cc_id'
);
END;
RETURN (v_func_code_combination_id);
END;
PROCEDURE get_vendor_id (
p_vendor_site_code IN VARCHAR2
,p_vendor_id OUT NUMBER
,p_vendor_site_id OUT NUMBER
,p_term_id OUT NUMBER
)
/* --------Procedure to get the vendor_id----------*/
IS
BEGIN
BEGIN
SELECT vendor_id
INTO p_vendor_id
FROM po_vendor_sites_all
WHERE vendor_site_code = p_vendor_site_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_vendor_id := 0;
END;
BEGIN
SELECT vendor_site_id
,NVL (terms_id, 0)
INTO p_vendor_site_id
,p_term_id
FROM po_vendor_sites_all
WHERE vendor_site_code = p_vendor_site_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_vendor_site_id := 0;
p_term_id := 0;
fnd_file.put_line (fnd_file.LOG
, 'Invalid vendor_site_code in oracle' || ' ' || p_vendor_site_code
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'in'
|| ''
|| 'vendor_site_code prcedure'
);
END;
PROCEDURE get_goods_receive_date (
p_po_no VARCHAR2
,p_line_no VARCHAR2
,p_shipment_no VARCHAR2
,p_goods_received_date VARCHAR2
)
AS
v_invoice_id ap_invoice_lines_interface.invoice_id%TYPE;
BEGIN
BEGIN
SELECT invoice_id
INTO v_invoice_id
FROM ap_invoice_lines_interface
WHERE po_number = p_po_no AND po_line_number = p_line_no
AND to_char(po_shipment_num) = p_shipment_no;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_invoice_id := 0;
fnd_file.put_line (fnd_file.LOG
, 'Invoice is not created for po#'
|| ' '
|| p_po_no
|| ' '
|| 'and shipment#'
|| ' '
|| p_shipment_no
);
WHEN TOO_MANY_ROWS
THEN
v_invoice_id := 0;
fnd_file.put_line (fnd_file.LOG
, 'Multiple Invoices are existing for po#'
|| ' '
|| p_po_no
|| ' '
|| 'line_num'
|| ' '
|| p_line_no
|| ' '
|| 'and shipment_num'
|| ' '
|| p_shipment_no
);
WHEN OTHERS
THEN
v_invoice_id := 0;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'in'
|| ''
|| 'Goods_receive_date prcedure'
);
END;
BEGIN
UPDATE ap_invoices_interface
SET goods_received_date = p_goods_received_date
WHERE invoice_id = v_invoice_id;
COMMIT;
IF v_invoice_id <> 0
THEN
fnd_file.put_line (fnd_file.LOG
, 'Goods_received_date'
|| ' '
|| p_goods_received_date
|| ' '
|| 'is updated for po#'
|| ' '
|| p_po_no
|| ' '
|| 'and shipment#'
|| ' '
|| p_shipment_no
|| ' '
|| 'in ap_invoces_interface table'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'in'
|| ''
|| 'Goods_receive_date prcedure'
);
END;
END;
PROCEDURE get_retek_records (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
/* --------Procedure to get the retet records and insert in PO base tables----------*/
CURSOR c1_fetch_po
IS
/* --------Main Cursor to get the records for PO_HEADERS_ALL---------*/
SELECT DISTINCT po_number
,buyer_user_id
,vendor_site_code
,currency_code
,ship_to_location
FROM xxhbc_3way_po_ship_iface
WHERE extract_flag IS NULL AND extract_date IS NULL AND reject_reason IS NULL;
CURSOR cur_get_line_info (p_po_number VARCHAR2)
IS
/* --------Main Cursor to get the records for PO_liNES_ALL---------*/
SELECT DISTINCT item_description
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) line_num
,quantity_ordered
,unit_price
,upc_number
,taxable_flag
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_po_number;
CURSOR cur_get_ship_info (p_ship_po_number VARCHAR2)
IS
/* --------Cursor to get the shipment records for PO_LINE_LOCATIONS_aLL---------*/
SELECT DISTINCT po_number
,shipment_num
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) line_num
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_ship_po_number;
CURSOR cur_get_line_loc (p_loc_po_number VARCHAR2, p_line_num VARCHAR2, p_shipment_num VARCHAR2)
IS
/* --------Cursor to get the records for PO_LINE_LOCATIONS_ALL---------*/
SELECT DISTINCT uom
,shipment_num
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) line_num
,unit_price
,goods_received_date
,ship_to_location
,tax_gst
,tax_hst
,tax_qst
,tax_province
, (quantity_shipped - quantity_received) quantity_rejected
,gl_accrual_segment1
,gl_accrual_segment2
,gl_accrual_segment3
,gl_accrual_segment4
,gl_accrual_segment5
,gl_accrual_segment6
,quantity_ordered
,quantity_received
,quantity_shipped
,set_of_books_id
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_loc_po_number
AND (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) = p_line_num
AND shipment_num = p_shipment_num;
v_po_header_id po_headers_all.po_header_id%TYPE := 0;
v_po_number po_headers_all.segment1%TYPE := NULL;
v_agent_id po_headers_all.agent_id%TYPE := 0;
v_vendor_id po_headers_all.vendor_id%TYPE := 0;
v_vendor_site_code_po po_vendor_sites_all.vendor_site_code%TYPE := NULL;
v_vendor_site_id_po po_vendor_sites_all.vendor_site_id%TYPE := 0;
v_vendor_site_code po_vendor_sites_all.vendor_site_code%TYPE := NULL;
v_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE := 0;
v_term_id ap_terms.term_id%TYPE := 0;
v_comb_term_id ap_terms.term_id%TYPE := 0;
v_po_process NUMBER := 0;
v_process NUMBER := 0;
v_user_process NUMBER := 0;
v_term_process NUMBER := 0;
v_vendor_site_process NUMBER := 0;
v_po_line_number po_lines_all.line_num%TYPE := NULL;
v_po_line_id po_lines_all.po_line_id%TYPE := 0;
v_lines_po_header_id po_lines_all.po_header_id%TYPE := 0;
v_po_line_id_locations po_line_locations_all.po_line_id%TYPE := 0;
v_po_line_location_id po_line_locations_all.line_location_id%TYPE := 0;
v_distribution_id po_distributions_all.po_distribution_id%TYPE := 0;
v_dis_line_location_id po_line_locations_all.line_location_id%TYPE := 0;
v_code_combination_id gl_code_combinations.code_combination_id%TYPE := 0;
v_upd_po_header_id po_headers_all.po_header_id%TYPE := 0;
v_line_id_po po_lines_all.po_line_id%TYPE := 0;
po_line_id po_lines_all.po_line_id%TYPE := 0;
v_po_line_process NUMBER := 0;
v_quantity_rejected NUMBER := 0;
v_quantity NUMBER := 0;
v_distribution_num NUMBER := 0;
l_rej_count NUMBER := 0;
l_po_count NUMBER := 0;
v_po_header_error NUMBER := 0;
v_po_line_error NUMBER := 0;
v_location_error NUMBER := 0;
v_code_comb_process NUMBER := 0;
v_vendor_process NUMBER := 0;
v_last_update_user_id NUMBER := 0;
v_user_id_process NUMBER := 0;
l_count_po NUMBER := 0;
l_count_line NUMBER := 0;
l_count_loc NUMBER := 0;
l_count_dis NUMBER := 0;
v_vendor_term_id NUMBER := 0;
l_exit_line_count NUMBER := 0;
l_total_rec_process NUMBER := 0;
v_shipment_num NUMBER := 0;
BEGIN
FOR rec_fetch_po IN c1_fetch_po
LOOP
l_count_po := l_count_po + 1;
v_distribution_num := 0;
IF check_existing_lines (rec_fetch_po.po_number) = 0 ---IF 0 then new PO will creat
THEN
fnd_file.put_line
(fnd_file.LOG
,'*********************************************************************'
);
fnd_file.put_line (fnd_file.LOG
, '**********PO Number'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| '**********'
|| 'Process From staging table'
);
BEGIN
------------------- ---Query to get the agent_id-------------------
SELECT get_agent_id (rec_fetch_po.buyer_user_id)
INTO v_agent_id
FROM DUAL;
IF v_agent_id = 0
THEN
v_user_process := 2;
UPDATE xxhbc_3way_po_ship_iface
SET reject_reason =
'buyer_user_id'
|| ' '
|| rec_fetch_po.buyer_user_id
|| ' '
|| 'does not exist in oracle'
,extract_flag = 'R'
,extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS')
WHERE po_number = rec_fetch_po.po_number;
fnd_file.put_line (fnd_file.LOG
, 'po#'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| 'is rejected due to invalid agent_id'
|| ' '
|| v_agent_id
);
ELSE
v_user_process := 1;
fnd_file.put_line (fnd_file.LOG
, 'agent_id is'
|| ' '
|| v_agent_id
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
----- variable will be 1 when agent_id is valid
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'calculation of agent_id'
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
END;
IF (v_user_process = 1)
THEN
BEGIN
---Query to get the vendor_id---------------------
get_vendor_id (rec_fetch_po.vendor_site_code
,v_vendor_id
,v_vendor_site_id
,v_vendor_term_id
);
IF v_vendor_id = 0
THEN
v_vendor_process := 2;
fnd_file.put_line (fnd_file.LOG
, 'po#'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| 'is rejected due to vendor_site_code'
|| ' '
|| rec_fetch_po.vendor_site_code
|| ' '
|| 'does not exist in oracle'
);
UPDATE xxhbc_3way_po_ship_iface
SET reject_reason =
'vendor_site_code'
|| ' '
|| rec_fetch_po.vendor_site_code
|| ' '
|| 'does not exist in oracle'
,extract_flag = 'R'
,extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS')
WHERE po_number = rec_fetch_po.po_number;
ELSE
v_vendor_process := 1;
fnd_file.put_line (fnd_file.LOG
, 'vendor_id is'
|| ' '
|| v_vendor_id
|| ' '
|| 'for vendor_site_code'
|| rec_fetch_po.vendor_site_code
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'calculation of vendor_id'
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
END;
END IF;
IF (v_vendor_process = 1)
THEN
BEGIN
---Query to get the term_id----------------------
SELECT get_term_id (rec_fetch_po.po_number)
INTO v_term_id
FROM DUAL;
IF (v_term_id = 0 AND v_vendor_term_id <> 0)
THEN
v_term_id := v_vendor_term_id;
fnd_file.put_line (fnd_file.LOG
, 'Term_id'
|| ' '
|| v_term_id
|| ' '
|| 'is defaulted from vendor_site_code'
|| ' '
|| v_vendor_site_code
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
v_term_process := 1;
ELSIF (v_term_id = 0 AND v_vendor_term_id = 0)
THEN
v_term_process := 2;
UPDATE xxhbc_3way_po_ship_iface
SET reject_reason =
'Term parameters are not exisiting in oracle to Derive term_id'
,extract_flag = 'R'
,extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS')
WHERE po_number = rec_fetch_po.po_number;
ELSE
v_term_process := 1;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'calculation of term_id'
|| 'for PO#'
|| ' '
|| rec_fetch_po.po_number
);
END;
END IF;
IF (v_user_process = 1 AND v_vendor_process = 1 AND v_term_process = 1)
THEN
BEGIN
INSERT INTO po_headers_all
(po_header_id
,segment1
,agent_id
,type_lookup_code
,vendor_id
,vendor_site_id
,terms_id
,currency_code
,approved_flag
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,summary_flag
,enabled_flag
,freight_terms_lookup_code
,ship_to_location_id
)
VALUES (po_headers_s.NEXTVAL
,rec_fetch_po.po_number
,v_agent_id
,'STANDARD'
,v_vendor_id
,v_vendor_site_id
,v_term_id
,rec_fetch_po.currency_code
,'Y'
,SYSDATE
,fnd_global.user_id -- v_last_update_user_id
,fnd_global.user_id --v_last_update_user_id
,SYSDATE
,fnd_global.user_id ---v_last_update_user_id
,'N'
,'Y'
,'PO'
,rec_fetch_po.ship_to_location
);
v_po_header_error := 1;
l_po_count := l_po_count + 1;
fnd_file.put_line (fnd_file.LOG
, 'PO Number'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| 'is inserted into po_headers_all'
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
v_po_header_error := 2;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| 'insertion in po_headers_all Table'
|| 'po#'
|| ' '
|| rec_fetch_po.po_number
);
END;
END IF;
ELSIF check_existing_lines (rec_fetch_po.po_number) = 1
THEN
--- ***** Validation if po_line is existing ***********
process_existing_lines (rec_fetch_po.po_number);
v_po_header_error := 2;
END IF;
-- ****** For po_lines_all *********
---Query to get the po_line_id for PO_LINES_ALL table
IF (v_po_header_error = 1)
THEN
FOR rec_get_line_info IN cur_get_line_info (rec_fetch_po.po_number)
LOOP
--- Query to get po_line_id from sequence
SELECT po_lines_s.NEXTVAL
INTO v_po_line_id
FROM DUAL;
BEGIN
INSERT INTO po_lines_all
(po_line_id
,po_header_id
,line_num
,item_description
,unit_price
,item_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,taxable_flag
,line_type_id
,quantity
)
VALUES (v_po_line_id
,po_headers_s.CURRVAL
,rec_get_line_info.line_num
,rec_get_line_info.item_description
,rec_get_line_info.unit_price
,rec_get_line_info.upc_number
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,fnd_global.user_id --v_last_update_user_id
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,rec_get_line_info.taxable_flag
,1
,rec_get_line_info.quantity_ordered
);
fnd_file.put_line
(fnd_file.LOG
,'--------------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG
, 'line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'is inserted into po_lines_all for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'line_id is'
|| ' '
|| v_po_line_id
|| ' '
|| 'for line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'sku_number is'
|| ' '
|| rec_get_line_info.upc_number
|| ' '
|| 'for line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'sku_description is'
|| ' '
|| rec_get_line_info.item_description
|| ' '
|| 'for line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'line_quantity is'
|| ' '
|| rec_get_line_info.quantity_ordered
|| ' '
|| 'for line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'unit_price is'
|| ' '
|| rec_get_line_info.unit_price
|| ' '
|| 'for line_num'
|| ' '
|| rec_get_line_info.line_num
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
v_po_line_error := 1;
EXCEPTION
WHEN OTHERS
THEN
v_po_line_error := 2;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'insertion in po_lines_all table po#'
|| ' '
|| rec_fetch_po.po_number
);
END;
END LOOP;
END IF;
--************************************** FOR po_line_locations_all --*************************
IF (v_po_header_error = 1 AND v_po_line_error = 1)
THEN
--- for line_location_id from PO_LOCATIONS_ALL table
FOR rec_get_ship_info IN cur_get_ship_info (rec_fetch_po.po_number)
LOOP
FOR rec_get_line_loc IN cur_get_line_loc (rec_fetch_po.po_number
,rec_get_ship_info.line_num
,rec_get_ship_info.shipment_num
)
LOOP
get_goods_receive_date (rec_fetch_po.po_number
,rec_get_line_loc.line_num
,rec_get_line_loc.shipment_num
,rec_get_line_loc.goods_received_date
);
v_code_combination_id :=
get_cc_id (rec_fetch_po.po_number
,rec_get_line_loc.line_num
,rec_get_line_loc.shipment_num
);
BEGIN
SELECT pla.po_line_id
,vendor_site_id
INTO v_line_id_po
,v_vendor_site_id_po
FROM po_headers_all pha, po_lines_all pla
WHERE pha.po_header_id = pla.po_header_id
AND pha.segment1 = rec_fetch_po.po_number
AND pla.line_num = rec_get_line_loc.line_num;
SELECT vendor_site_code
INTO v_vendor_site_code_po
FROM po_vendor_sites_all
WHERE vendor_site_id = v_vendor_site_id_po;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG
, 'No New Po Line found For po#'
|| ' '
|| rec_fetch_po.po_number
);
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'in' || ''
|| 'Vendor site_id and vendor_site_code'
);
END;
IF v_code_combination_id = 88663
THEN
fnd_file.put_line
(fnd_file.LOG
, 'cc_id is defaulted to 88663 as it can not derived from the segment combinations for po#'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| 'line#'
|| ' '
|| rec_get_line_loc.line_num
|| ' '
|| 'and shipment#'
|| ' '
|| rec_get_line_loc.shipment_num
);
ELSE
fnd_file.put_line (fnd_file.LOG
, 'accrual_account_id'
|| ' '
|| v_code_combination_id
|| ' '
|| 'is defined for po#'
|| ' '
|| rec_fetch_po.po_number
|| ' '
|| 'line_num'
|| ' '
|| rec_get_line_loc.line_num
|| ' '
|| 'and shipment#'
|| ' '
|| rec_get_line_loc.shipment_num
);
END IF;
BEGIN
SELECT po_line_locations_s.NEXTVAL
INTO v_po_line_location_id
FROM DUAL;
v_shipment_num :=
xxhbc_3way_utl.get_ship_alias (rec_get_line_loc.shipment_num
,v_vendor_site_code_po
,'PO'
);
INSERT INTO po_line_locations_all
(line_location_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,po_header_id
,po_line_id
,quantity
,quantity_received
,unit_meas_lookup_code
,ship_to_location_id
,approved_flag
,shipment_num
,shipment_type
,inspection_required_flag
,receipt_required_flag
,closed_code
,price_override
,attribute14
,attribute13
,quantity_rejected
,attribute1
,attribute2
,attribute3
,attribute4
)
VALUES (v_po_line_location_id
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,fnd_global.user_id --v_last_update_user_id
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,po_headers_s.CURRVAL
,v_line_id_po
,rec_get_line_loc.quantity_ordered
,rec_get_line_loc.quantity_received
,rec_get_line_loc.uom
,rec_get_line_loc.ship_to_location
,'Y'
,v_shipment_num
,'STANDARD'
,'N'
,'Y'
,'OPEN'
,rec_get_line_loc.unit_price
,'RETEK'
,rec_get_line_loc.goods_received_date
,rec_get_line_loc.quantity_rejected
,rec_get_line_loc.tax_province
,rec_get_line_loc.tax_gst
,rec_get_line_loc.tax_hst
,rec_get_line_loc.tax_qst
-- ,rec_get_line_loc.tax_province -- modified to have province in attribut1
);
v_location_error := 1;
fnd_file.put_line (fnd_file.LOG
, 'line_locations_id'
|| ' '
|| v_po_line_location_id
|| ' '
|| 'is inserted into po_line_locations_all for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'shipment_number is'
|| ' '
|| rec_get_line_loc.shipment_num
|| ' '
|| 'for'
|| ' '
|| 'line_locations_id'
|| ' '
|| v_po_line_location_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
EXCEPTION
WHEN OTHERS
THEN
v_location_error := 2;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'Inserting into po_line_locations table'
);
END;
--******* Validation for po_distribution_table***********--- For po_distribution_id
v_distribution_num := v_distribution_num + 1;
SELECT po_distributions_s.NEXTVAL
INTO v_distribution_id
FROM DUAL;
BEGIN
INSERT INTO po_distributions_all
(po_distribution_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,po_header_id
,po_line_id
,line_location_id
,set_of_books_id
,code_combination_id
,accrual_account_id
,variance_account_id
,distribution_num
,quantity_ordered
,quantity_delivered
)
VALUES (v_distribution_id
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,fnd_global.user_id --v_last_update_user_id
,SYSDATE
,fnd_global.user_id --v_last_update_user_id
,po_headers_s.CURRVAL
,v_line_id_po
,v_po_line_location_id
,rec_get_line_loc.set_of_books_id
,v_code_combination_id
,v_code_combination_id
,NULL
,v_distribution_num
,rec_get_line_loc.quantity_ordered
,rec_get_line_loc.quantity_received
);
fnd_file.put_line (fnd_file.LOG
, 'line_distribution_id'
|| v_distribution_id
|| ' '
|| 'is inserted in po_distributions_all for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'distribution_number is'
|| ' '
|| v_distribution_num
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_ordered is'
|| ' '
|| rec_get_line_loc.quantity_ordered
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_received is'
|| ' '
|| rec_get_line_loc.quantity_received
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_shipped is'
|| ' '
|| rec_get_line_loc.quantity_shipped
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_rejected is'
|| ' '
|| rec_get_line_loc.quantity_rejected
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| rec_fetch_po.po_number
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'Inserting into po_line_locations table'
);
END;
END LOOP;
END LOOP;
END IF;
BEGIN
UPDATE xxhbc_3way_po_ship_iface
SET extract_flag = 'Y'
,extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS')
WHERE extract_flag IS NULL
AND extract_date IS NULL
AND reject_reason IS NULL
AND po_number = rec_fetch_po.po_number;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ' '
|| 'HBC 3way PO Match Program'
);
END;
BEGIN
SELECT COUNT (pla.po_line_id)
INTO l_count_line
FROM po_headers_all pha, po_lines_all pla
WHERE pha.po_header_id = pla.po_header_id
AND pha.segment1 = rec_fetch_po.po_number
AND TO_CHAR (pla.creation_date, 'YYYYMMDDHH24MI') =
TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI');
SELECT COUNT (pll.line_location_id)
INTO l_count_loc
FROM po_headers_all pha, po_lines_all pla, po_line_locations_all pll
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pla.po_line_id = pll.po_line_id
AND pha.segment1 = rec_fetch_po.po_number
AND TO_CHAR (pll.creation_date, 'YYYYMMDDHH24MI') =
TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI');
SELECT COUNT (pda.po_distribution_id)
INTO l_count_dis
FROM po_headers_all pha
,po_lines_all pla
,po_line_locations_all pll
,po_distributions_all pda
WHERE pha.po_header_id = pla.po_header_id
AND pha.po_header_id = pll.po_header_id
AND pha.po_header_id = pda.po_header_id
AND pll.line_location_id = pda.line_location_id
AND pla.po_line_id = pll.po_line_id
AND pla.po_line_id = pda.po_line_id
AND pha.segment1 = rec_fetch_po.po_number
AND TO_CHAR (pda.creation_date, 'YYYYMMDDHH24MI') =
TO_CHAR (SYSDATE, 'YYYYMMDDHH24MI');
END;
fnd_file.put_line (fnd_file.LOG
, 'Total Number Of lines created='
|| ' '
|| l_count_line
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'Total Number Of line locations created='
|| ' '
|| l_count_loc
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line (fnd_file.LOG
, 'Total Number Of line distributions created='
|| ' '
|| l_count_dis
|| ' '
|| 'for po#'
|| ' '
|| rec_fetch_po.po_number
);
fnd_file.put_line
(fnd_file.LOG
,'-------------------------------------------------------------------------------'
);
END LOOP;
COMMIT;
fnd_file.put_line (fnd_file.LOG
, '*****************Total Number Of po process from custom table='
|| ' '
|| l_count_po
|| ' '
|| '*****************'
);
fnd_file.put_line (fnd_file.LOG
, '*****************Total Number Of po created='
|| ' '
|| l_po_count
|| ' '
|| '*****************'
);
BEGIN
SELECT COUNT (po_number)
INTO l_rej_count
FROM xxhbc_3way_po_ship_iface
WHERE extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS') AND extract_flag = 'R';
fnd_file.put_line (fnd_file.LOG
, '*****************Total Number Of New Records rejected ='
|| ' '
|| l_rej_count
|| ' '
|| '*****************'
);
END;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'in' || ''
|| 'po 3way procedure'
);
END;
PROCEDURE process_existing_lines (p_po_no VARCHAR2)
/*
This procedure is used to create the new retak po lines if PO# is alraady existing in data base
*/
AS
CURSOR cur_get_polines
IS
SELECT DISTINCT po_number
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) line_num
,shipment_num
FROM xxhbc_3way_po_ship_iface
WHERE extract_flag IS NULL
AND extract_date IS NULL
AND reject_reason IS NULL
AND po_number = p_po_no;
CURSOR cur_get_line_loc (p_loc_po_number VARCHAR2, p_line_no VARCHAR2, p_shipment_num VARCHAR2)
IS
/* --------Cursor to get the records for PO_LINE_LOCATIONS_ALL---------*/
SELECT DISTINCT item_description
,uom
,taxable_flag
,shipment_num
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) line_num
,unit_price
,goods_received_date
,ship_to_location
,tax_gst
,tax_hst
,tax_qst
,tax_province
, (quantity_shipped - quantity_received) quantity_rejected
,gl_accrual_segment1
,gl_accrual_segment2
,gl_accrual_segment3
,gl_accrual_segment4
,gl_accrual_segment5
,gl_accrual_segment6
,quantity_ordered
,quantity_received
,quantity_shipped
,set_of_books_id
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_loc_po_number
AND (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) = p_line_no
AND shipment_num = p_shipment_num;
v_po_line_number po_lines_all.line_num%TYPE := NULL;
v_proc_line_id po_lines_all.po_line_id%TYPE := 0;
v_lines_po_header_id po_lines_all.po_header_id%TYPE := 0;
v_po_line_id po_line_locations_all.po_line_id%TYPE := 0;
v_po_line_location_id po_line_locations_all.line_location_id%TYPE := 0;
v_distribution_id po_distributions_all.po_distribution_id%TYPE := 0;
v_dis_line_location_id po_line_locations_all.line_location_id%TYPE := 0;
v_code_combination_id gl_code_combinations.code_combination_id%TYPE := 0;
v_upd_po_header_id po_headers_all.po_header_id%TYPE := 0;
v_upd_po_line_id po_lines_all.po_line_id%TYPE := 0;
po_line_id po_lines_all.po_line_id%TYPE := 0;
v_po_line_process NUMBER := 0;
v_quantity_rejected NUMBER := 0;
v_quantity NUMBER := 0;
v_distribution_num NUMBER := 0;
l_rej_count NUMBER := 0;
l_po_count NUMBER := 0;
v_po_header_error NUMBER := 0;
v_po_line_error NUMBER := 0;
v_location_error NUMBER := 0;
v_code_comb_process NUMBER := 0;
v_vendor_process NUMBER := 0;
v_last_update_user NUMBER := 0;
v_user_id_process NUMBER := 0;
l_count_po NUMBER := 0;
l_count_line_proc NUMBER := 0;
l_count_loc_proc NUMBER := 0;
l_count_dis_proc NUMBER := 0;
v_po_hed_id po_headers_all.po_header_id%TYPE := 0;
v_item_description VARCHAR2 (100) := NULL;
v_uom VARCHAR2 (40) := NULL;
v_taxable_flag VARCHAR2 (40) := NULL;
v_line_num po_lines_all.line_num%TYPE := NULL;
v_quantity_ordered NUMBER := 0;
v_upc_number NUMBER := 0;
v_unit_price NUMBER := 0;
v_proc_agent_id po_headers_all.agent_id%TYPE := 0;
v_proc_vendor_id po_headers_all.vendor_id%TYPE := 0;
v_proc_term_id po_headers_all.terms_id%TYPE := 0;
v_vendor_site_code_proc po_vendor_sites_all.vendor_site_code%TYPE := NULL;
v_vendor_site_id_proc po_vendor_sites_all.vendor_site_id%TYPE := 0;
v_shipment_num_proc NUMBER := 0;
BEGIN
fnd_file.put_line (fnd_file.LOG
, '************'
|| 'po#'
|| ' '
|| p_po_no
|| ' '
|| 'is existing in data base************'
);
BEGIN
SELECT po_header_id
,agent_id
,vendor_id
,terms_id
,vendor_site_id
INTO v_po_hed_id
,v_proc_agent_id
,v_proc_vendor_id
,v_proc_term_id
,v_vendor_site_id_proc
FROM po_headers_all
WHERE segment1 = p_po_no;
SELECT vendor_site_code
INTO v_vendor_site_code_proc
FROM po_vendor_sites_all
WHERE vendor_site_id = v_vendor_site_id_proc;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG, 'NO PO FOUND in PROCEDURE process_existing_line');
END;
fnd_file.put_line (fnd_file.LOG
, 'agent_id is ' || ' ' || v_proc_agent_id || ' ' || 'for po#' || ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'vendor_id is' || ' ' || v_proc_vendor_id || ' ' || 'for po#' || ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'term_id is ' || ' ' || v_proc_term_id || ' ' || 'for po#' || ' '
|| p_po_no
);
FOR rec_get_polines IN cur_get_polines
LOOP
FOR rec_get_line_loc IN cur_get_line_loc (p_po_no
,rec_get_polines.line_num
,rec_get_polines.shipment_num
)
LOOP
IF check_shipment_lines (p_po_no, rec_get_polines.line_num) = 0
THEN
BEGIN
SELECT DISTINCT item_description
,uom
,taxable_flag
, (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0'))
line_num
,quantity_ordered
,upc_number
,unit_price
INTO v_item_description
,v_uom
,v_taxable_flag
,v_line_num
,v_quantity_ordered
,v_upc_number
,v_unit_price
FROM xxhbc_3way_po_ship_iface
WHERE po_number = p_po_no
AND (upc_number || LPAD (TO_CHAR (ship_to_location), 4, '0')) =
rec_get_polines.line_num
AND shipment_num = rec_get_polines.shipment_num;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG
,'NO PO Shipment FOUND in PROCEDURE process_existing_line'
);
END;
BEGIN
SELECT po_lines_s.NEXTVAL
INTO v_proc_line_id
FROM DUAL;
INSERT INTO po_lines_all
(po_line_id
,po_header_id
,line_num
,item_description
,unit_price
,item_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,taxable_flag
,line_type_id
,quantity
)
VALUES (po_lines_s.NEXTVAL
,v_po_hed_id
,v_line_num
,v_item_description
,v_unit_price
,v_upc_number
,SYSDATE
,fnd_global.user_id --v_last_update_user
,fnd_global.user_id --v_last_update_user
,SYSDATE
,fnd_global.user_id --v_last_update_user
,v_taxable_flag
,1
,v_quantity_ordered
);
fnd_file.put_line (fnd_file.LOG
, '************'
|| 'New line#'
|| ' '
|| rec_get_polines.line_num
|| ' '
|| 'is created for po#'
|| ' '
|| p_po_no
|| ' '
|| '************'
);
v_po_line_error := 1;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_po_line_error := 2;
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'insertion in po_lines_all table'
);
END;
END IF;
--************************************** FOR po_line_locations_all --*************************
--- for line_location_id from PO_LOCATIONS_ALL table
fnd_file.put_line
(fnd_file.LOG
,'--------------------------------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG
, 'New shipment_number'
|| ' '
|| rec_get_line_loc.shipment_num
|| ' '
|| 'is'
|| ' '
|| 'created '
|| 'for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'line_num'
|| ' '
|| rec_get_polines.line_num
|| ' '
|| 'is inserted into po_lines_all for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'line_id is'
|| ' '
|| v_proc_line_id
|| ' '
|| 'for line_num'
|| ' '
|| v_line_num
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'sku_number is'
|| ' '
|| v_upc_number
|| ' '
|| 'for line_num'
|| ' '
|| v_line_num
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'sku_description is'
|| ' '
|| v_item_description
|| ' '
|| 'for line_num'
|| ' '
|| v_line_num
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'line_quantity is'
|| ' '
|| v_quantity_ordered
|| ' '
|| 'for line_num'
|| ' '
|| v_line_num
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'unit_price is'
|| ' '
|| v_unit_price
|| ' '
|| 'for line_num'
|| ' '
|| v_line_num
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
v_shipment_num_proc :=
xxhbc_3way_utl.get_ship_alias (rec_get_line_loc.shipment_num
,v_vendor_site_code_proc
,'PO');
get_goods_receive_date (p_po_no
,rec_get_line_loc.line_num
,v_shipment_num_proc
,rec_get_line_loc.goods_received_date
);
v_code_combination_id :=
get_cc_id (p_po_no, rec_get_line_loc.line_num, rec_get_line_loc.shipment_num);
IF v_code_combination_id = 88663
THEN
fnd_file.put_line
(fnd_file.LOG
, 'cc_id is defaulted to 88663 as it can not derived from the segment combinations for po#'
|| ' '
|| p_po_no
|| ' '
|| 'line#'
|| ' '
|| rec_get_line_loc.line_num
|| ' '
|| 'and shipment#'
|| ' '
|| rec_get_line_loc.shipment_num
);
ELSE
fnd_file.put_line (fnd_file.LOG
, 'cc_id is'
|| ' '
|| v_code_combination_id
|| ' '
|| 'for po#'
|| ' '
|| p_po_no
|| ' '
|| 'line#'
|| ' '
|| rec_get_line_loc.line_num
|| ' '
|| 'and shipment#'
|| ' '
|| rec_get_line_loc.shipment_num
);
END IF;
BEGIN
SELECT po_line_id
INTO v_po_line_id
FROM po_lines_all pla
WHERE po_header_id = v_po_hed_id AND line_num = rec_get_polines.line_num;
END;
SELECT po_line_locations_s.NEXTVAL
INTO v_po_line_location_id
FROM DUAL;
BEGIN
INSERT INTO po_line_locations_all
(line_location_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,po_header_id
,po_line_id
,quantity
,quantity_received
,unit_meas_lookup_code
,ship_to_location_id
,approved_flag
,shipment_num
,shipment_type
,inspection_required_flag
,receipt_required_flag
,closed_code
,price_override
,attribute14
,attribute13
,quantity_rejected
,attribute1
,attribute2
,attribute3
,attribute4
)
VALUES (po_line_locations_s.NEXTVAL
,SYSDATE
,fnd_global.user_id --v_last_update_user
,fnd_global.user_id --v_last_update_user
,SYSDATE
,fnd_global.user_id --v_last_update_user
,v_po_hed_id
,v_po_line_id --po_lines_s.CURRVAL
,rec_get_line_loc.quantity_ordered
,rec_get_line_loc.quantity_received
,rec_get_line_loc.uom
,rec_get_line_loc.ship_to_location
,'Y'
,v_shipment_num_proc
,'STANDARD'
,'N'
,'Y'
,'OPEN'
,rec_get_line_loc.unit_price
,'RETEK'
,rec_get_line_loc.goods_received_date
,rec_get_line_loc.quantity_rejected
,rec_get_line_loc.tax_province
,rec_get_line_loc.tax_gst
,rec_get_line_loc.tax_hst
,rec_get_line_loc.tax_qst
--,rec_get_line_loc.tax_province -- modified to have province on attribute1
);
v_location_error := 1;
fnd_file.put_line (fnd_file.LOG
, 'line_locations_id'
|| ' '
|| v_po_line_location_id
|| ' '
|| 'is inserted into po_line_locations_all for po#'
|| ' '
|| p_po_no
);
EXCEPTION
WHEN OTHERS
THEN
v_location_error := 2;
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'Inserting into po_line_locations table'
);
END;
--******* Validation for po_distribution_table***********--- For po_distribution_id
SELECT MAX (distribution_num)
INTO v_distribution_num
FROM po_distributions_all
WHERE po_header_id = v_po_hed_id;
SELECT po_distributions_s.NEXTVAL
INTO v_distribution_id
FROM DUAL;
v_distribution_num := v_distribution_num + 1;
BEGIN
INSERT INTO po_distributions_all
(po_distribution_id
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,po_header_id
,po_line_id
,line_location_id
,set_of_books_id
,code_combination_id
,accrual_account_id
,variance_account_id
,distribution_num
,quantity_ordered
,quantity_delivered
)
VALUES (po_distributions_s.NEXTVAL
,SYSDATE
,fnd_global.user_id --v_last_update_user
,fnd_global.user_id --v_last_update_user
,SYSDATE
,fnd_global.user_id --v_last_update_user
,v_po_hed_id
,v_po_line_id --po_lines_s.CURRVAL
,po_line_locations_s.CURRVAL
,rec_get_line_loc.set_of_books_id
,v_code_combination_id
,v_code_combination_id
,NULL
,v_distribution_num
,rec_get_line_loc.quantity_ordered
,rec_get_line_loc.quantity_received
);
fnd_file.put_line (fnd_file.LOG
, 'line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'is inserted in po_distributions_all for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'distribution_number is'
|| ' '
|| v_distribution_num
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_ordered is'
|| ' '
|| rec_get_line_loc.quantity_ordered
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_received is'
|| ' '
|| rec_get_line_loc.quantity_received
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_shipped is'
|| ' '
|| rec_get_line_loc.quantity_shipped
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line (fnd_file.LOG
, 'quantity_rejected is'
|| ' '
|| rec_get_line_loc.quantity_rejected
|| ' '
|| 'for line_distribution_id'
|| ' '
|| v_distribution_id
|| ' '
|| 'and for po#'
|| ' '
|| p_po_no
);
fnd_file.put_line
(fnd_file.LOG
,'--------------------------------------------------------------------------------------'
);
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ''
|| 'Inserting into po_line_locations table'
);
END;
BEGIN
UPDATE xxhbc_3way_po_ship_iface
SET extract_flag = 'Y'
,extract_date = TO_CHAR (SYSDATE, 'YYYYMMDDSS')
WHERE extract_flag IS NULL
AND extract_date IS NULL
AND reject_reason IS NULL
AND po_number = p_po_no;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****'
|| ''
|| SQLERRM
|| '*****'
|| 'for'
|| ' '
|| 'Procedure process_existing_lines'
);
END;
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
fnd_file.put_line (fnd_file.LOG
, '*****' || '' || SQLERRM || '*****' || 'in' || ''
|| 'po 3way procedure'
);
END;
END xxhbc_3way_po_match_pkg;
/