/* Formatted on 12-08-2011 00:36:38 (QP5 v5.115.810.9015) */
/* CREATE TABLE xx_r12tax_load
(
Operating_unit VARCHAR2 (75),
Po_no VARCHAR2 (25),
po_line_no NUMBER,
tax_name VARCHAR2 (50),
tax_type VARCHAR2 (50),
tax_line_no NUMBER,
precedence_1 NUMBER,
precedence_2 NUMBER,
precedence_3 NUMBER,
precedence_4 NUMBER,
precedence_5 NUMBER,
tax_rate NUMBER,
tax_amount NUMBER,
Loaded_status varchar2(4)
);
DROP TABLE xx_r12tax_load; */
CREATE OR REPLACE PROCEDURE APPS.XX_TAX_LOAD (retcode OUT VARCHAR2,
errbuf OUT VARCHAR2)
IS
CURSOR c1
IS
SELECT * FROM xx_r12tax_load;
CURSOR c2 (po_header_id IN NUMBER)
IS
SELECT *
FROM po_lines_all
WHERE po_header_id = po_header_id
ORDER BY po_header_id, po_line_id, LINE_NUM;
CURSOR c3 (po_no IN NUMBER, po_line_no NUMBER)
IS
SELECT OPERATING_UNIT,
TO_NUMBER (PO_NO),
PO_LINE_NO,
TAX_NAME,
TAX_TYPE,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_RATE,
TAX_AMOUNT
FROM xx_r12tax_load
WHERE PO_NO = po_no AND PO_LINE_NO = po_line_no
ORDER BY TO_NUMBER (PO_NO), PO_LINE_NO, TAX_LINE_NO;
v_rec_count NUMBER;
v_operating_unit VARCHAR2 (50);
v_po_count NUMBER;
v_po_header_id NUMBER;
v_po_line_cnt NUMBER;
v_po_line_id NUMBER;
v_po_tax_count NUMBER;
v_po_tax_lne_cnt NUMBER;
v_tax_id VARCHAR2 (50);
v_po_line_locid NUMBER;
v_po_ln_focid NUMBER;
BEGIN
FOR rec1 IN c1
LOOP
DBMS_OUTPUT.put_line( '*************************************'
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| rec1.PO_NO
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
INTO v_rec_count
FROM hr_operating_units
WHERE name = rec1.OPERATING_UNIT;
END;
IF v_rec_count > 0
THEN
BEGIN
SELECT COUNT ( * ), po_header_id
--------------------------------------------------------------------------------
INTO v_po_count, v_po_header_id
FROM po_headers_all
WHERE segment1 = rec1.Po_no
GROUP BY po_header_id;
IF v_po_count > 0
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************'
);
BEGIN
-- FOR rec3 IN c3 (v_po_header_id)
-- LOOP
-- DBMS_OUTPUT.put_line ('Test');
-- END LOOP;
-- SELECT COUNT ( * ), po_line_id
-- INTO v_po_line_cnt, v_po_line_id
-- FROM po_lines_all
-- WHERE po_header_id = v_po_header_id;
-- IF v_po_line_cnt > 0
-- THEN
BEGIN
FOR rec2 IN c2 (v_po_header_id)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************'
);
/* BEGIN
SELECT line_location_id, line_focus_id
INTO v_po_line_locid, v_po_ln_focid
FROM jai_po_line_locations
WHERE po_header_id = rec2.po_header_id
AND po_line_id = rec2.po_line_id;
DBMS_OUTPUT.put_line(' line_location_id, line_focus_id ' --------------------------------------------------------------------------------
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid);
fnd_file.put_line (
fnd_file.LOG,
' line_location_id, line_focus_id '
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid
);
END; */
BEGIN
FOR rec3 IN c3 (rec1.Po_no, rec2.LINE_NUM)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
-- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
-- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
--------------------------------------------------------------------------------
INTO v_po_tax_count
FROM JAI_PO_TAXES
WHERE PO_HEADER_ID = v_po_header_id
AND po_line_id = rec2.po_line_id
AND TAX_LINE_NO = rec3.TAX_LINE_NO;
IF v_po_tax_count > 0
THEN
BEGIN
SELECT tax_id
INTO v_tax_id
FROM jai_cmn_taxes_all
WHERE UPPER (tax_name) =
UPPER (rec3.TAX_NAME);
DBMS_OUTPUT.put_line('*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER(rec3.TAX_NAME)
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER (rec3.TAX_NAME)
|| '********************************'
);
END;
ELSE
IF rec3.TAX_NAME LIKE '%'
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_TYPE,
TAX_AMOUNT,
LINE_FOCUS_ID,
LINE_LOCATION_ID
)
VALUES (v_tax_id,
rec3.PRECEDENCE_1,
rec3.PRECEDENCE_2,
rec3.PRECEDENCE_3,
rec3.PRECEDENCE_4,
rec3.PRECEDENCE_5,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_TYPE,
rec3.TAX_AMOUNT,
13620,
167001);
COMMIT;
ELSE
BEGIN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_AMOUNT,
TAX_TYPE
)
VALUES (v_tax_id,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_AMOUNT,
rec3.TAX_TYPE);
COMMIT;
END;
END IF;
END IF;
END;
END LOOP;
END;
END LOOP;
END;
-- ELSE
-- DBMS_OUTPUT.put_line ('Test');
-- END IF;
END;
ELSE
DBMS_OUTPUT.put_line('PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO);
fnd_file.put_line --------------------------------------------------------------------------------
(
fnd_file.LOG,
'PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO
);
END IF;
DBMS_OUTPUT.put_line ('Before end of the loop');
END;
ELSE
DBMS_OUTPUT.put_line ('End of the Loop ');
END IF;
END LOOP;
END;
/* CREATE TABLE xx_r12tax_load
(
Operating_unit VARCHAR2 (75),
Po_no VARCHAR2 (25),
po_line_no NUMBER,
tax_name VARCHAR2 (50),
tax_type VARCHAR2 (50),
tax_line_no NUMBER,
precedence_1 NUMBER,
precedence_2 NUMBER,
precedence_3 NUMBER,
precedence_4 NUMBER,
precedence_5 NUMBER,
tax_rate NUMBER,
tax_amount NUMBER,
Loaded_status varchar2(4)
);
DROP TABLE xx_r12tax_load; */
CREATE OR REPLACE PROCEDURE APPS.XX_TAX_LOAD (retcode OUT VARCHAR2,
errbuf OUT VARCHAR2)
IS
CURSOR c1
IS
SELECT * FROM xx_r12tax_load;
CURSOR c2 (po_header_id IN NUMBER)
IS
SELECT *
FROM po_lines_all
WHERE po_header_id = po_header_id
ORDER BY po_header_id, po_line_id, LINE_NUM;
CURSOR c3 (po_no IN NUMBER, po_line_no NUMBER)
IS
SELECT OPERATING_UNIT,
TO_NUMBER (PO_NO),
PO_LINE_NO,
TAX_NAME,
TAX_TYPE,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_RATE,
TAX_AMOUNT
FROM xx_r12tax_load
WHERE PO_NO = po_no AND PO_LINE_NO = po_line_no
ORDER BY TO_NUMBER (PO_NO), PO_LINE_NO, TAX_LINE_NO;
v_rec_count NUMBER;
v_operating_unit VARCHAR2 (50);
v_po_count NUMBER;
v_po_header_id NUMBER;
v_po_line_cnt NUMBER;
v_po_line_id NUMBER;
v_po_tax_count NUMBER;
v_po_tax_lne_cnt NUMBER;
v_tax_id VARCHAR2 (50);
v_po_line_locid NUMBER;
v_po_ln_focid NUMBER;
BEGIN
FOR rec1 IN c1
LOOP
DBMS_OUTPUT.put_line( '*************************************'
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| rec1.PO_NO
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
INTO v_rec_count
FROM hr_operating_units
WHERE name = rec1.OPERATING_UNIT;
END;
IF v_rec_count > 0
THEN
BEGIN
SELECT COUNT ( * ), po_header_id
--------------------------------------------------------------------------------
INTO v_po_count, v_po_header_id
FROM po_headers_all
WHERE segment1 = rec1.Po_no
GROUP BY po_header_id;
IF v_po_count > 0
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************'
);
BEGIN
-- FOR rec3 IN c3 (v_po_header_id)
-- LOOP
-- DBMS_OUTPUT.put_line ('Test');
-- END LOOP;
-- SELECT COUNT ( * ), po_line_id
-- INTO v_po_line_cnt, v_po_line_id
-- FROM po_lines_all
-- WHERE po_header_id = v_po_header_id;
-- IF v_po_line_cnt > 0
-- THEN
BEGIN
FOR rec2 IN c2 (v_po_header_id)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************'
);
/* BEGIN
SELECT line_location_id, line_focus_id
INTO v_po_line_locid, v_po_ln_focid
FROM jai_po_line_locations
WHERE po_header_id = rec2.po_header_id
AND po_line_id = rec2.po_line_id;
DBMS_OUTPUT.put_line(' line_location_id, line_focus_id ' --------------------------------------------------------------------------------
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid);
fnd_file.put_line (
fnd_file.LOG,
' line_location_id, line_focus_id '
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid
);
END; */
BEGIN
FOR rec3 IN c3 (rec1.Po_no, rec2.LINE_NUM)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
-- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
-- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
--------------------------------------------------------------------------------
INTO v_po_tax_count
FROM JAI_PO_TAXES
WHERE PO_HEADER_ID = v_po_header_id
AND po_line_id = rec2.po_line_id
AND TAX_LINE_NO = rec3.TAX_LINE_NO;
IF v_po_tax_count > 0
THEN
BEGIN
SELECT tax_id
INTO v_tax_id
FROM jai_cmn_taxes_all
WHERE UPPER (tax_name) =
UPPER (rec3.TAX_NAME);
DBMS_OUTPUT.put_line('*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER(rec3.TAX_NAME)
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER (rec3.TAX_NAME)
|| '********************************'
);
END;
ELSE
IF rec3.TAX_NAME LIKE '%'
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_TYPE,
TAX_AMOUNT,
LINE_FOCUS_ID,
LINE_LOCATION_ID
)
VALUES (v_tax_id,
rec3.PRECEDENCE_1,
rec3.PRECEDENCE_2,
rec3.PRECEDENCE_3,
rec3.PRECEDENCE_4,
rec3.PRECEDENCE_5,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_TYPE,
rec3.TAX_AMOUNT,
13620,
167001);
COMMIT;
ELSE
BEGIN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_AMOUNT,
TAX_TYPE
)
VALUES (v_tax_id,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_AMOUNT,
rec3.TAX_TYPE);
COMMIT;
END;
END IF;
END IF;
END;
END LOOP;
END;
END LOOP;
END;
-- ELSE
-- DBMS_OUTPUT.put_line ('Test');
-- END IF;
END;
ELSE
DBMS_OUTPUT.put_line('PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO);
fnd_file.put_line --------------------------------------------------------------------------------
(
fnd_file.LOG,
'PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO
);
END IF;
DBMS_OUTPUT.put_line ('Before end of the loop');
END;
ELSE
DBMS_OUTPUT.put_line ('End of the Loop ');
END IF;
END LOOP;
END;
No comments:
Post a Comment