Requirement : To update the Costing information of all elements in one payroll to other.
Step1: creating staging table XX_UPDATE_LINK_COSTING_NOL.
step2: insert into staging table using select script with required columns.
Step3: run the plsql block or procdure.
1.
CREATE TABLE APPS.XX_UPDATE_LINK_COSTING_NOL
(
ELEMENT_TYPE_ID NUMBER,
COST_SEGMENT1 NUMBER,
COST_SEGMENT2 NUMBER,
COST_SEGMENT3 NUMBER,
COST_SEGMENT4 NUMBER,
COST_SEGMENT5 NUMBER,
COST_SEGMENT6 NUMBER,
COST_SEGMENT7 NUMBER,
BALANCE_SEGMENT1 NUMBER,
BALANCE_SEGMENT2 NUMBER,
BALANCE_SEGMENT3 NUMBER,
BALANCE_SEGMENT4 NUMBER,
BALANCE_SEGMENT5 NUMBER,
BALANCE_SEGMENT6 NUMBER,
BALANCE_SEGMENT7 NUMBER,
VALID_FLAG VARCHAR2(1 BYTE), --Default 'N'
ERR VARCHAR2(2000 BYTE),
EXTRA_INFO1 VARCHAR2(200 BYTE), --Payroll _Id
EXTRA_INFO2 VARCHAR2(200 BYTE),
EXTRA_INFO3 VARCHAR2(200 BYTE)
)
2.
insert into xx_update_link_costing_nol
SELECT element_type_id,
cost.segment1,cost.segment2, cost.segment3, cost.segment4, cost.segment5, cost.segment6, cost.segment7,
balance.segment1,balance.segment2, balance.segment3, balance.segment4, balance.segment5, balance.segment6, balance.segment7,
'N', --default value
null,
61,--Payroll id to which we are going to update
null,
null
FROM
PAY_COST_ALLOCATION_KEYFLEX cost,
PAY_COST_ALLOCATION_KEYFLEX balance,
PAY_ELEMENT_LINKS_F pelf
WHERE
pelf.COST_ALLOCATION_KEYFLEX_ID = cost.COST_ALLOCATION_KEYFLEX_ID
and pelf.BALANCING_KEYFLEX_ID = balance.COST_ALLOCATION_KEYFLEX_ID
and ELEMENT_LINK_ID = 1922--taking only element or testing
COMMIT
3.
CREATE OR REPLACE PROCEDURE P_UPDATE_ELEMENT_ENTRY
AS
--DECLARE
p_effective_date DATE;
v_err VARCHAR2 (1000);
p_object_version_number NUMBER;
p_cost_allocation_keyflex_id NUMBER;
p_balancing_keyflex_id NUMBER;
p_cost_concat_segments_out VARCHAR2 (250);
p_balance_concat_segments_out VARCHAR2 (250);
p_effective_start_date DATE;
p_effective_end_date DATE;
-------------------------------
p_cost_segment1 VARCHAR2 (50) := NULL;
p_cost_segment2 VARCHAR2 (50) := NULL;
p_cost_segment3 VARCHAR2 (50) := NULL;
p_cost_segment4 VARCHAR2 (50) := NULL;
p_cost_segment5 VARCHAR2 (50) := NULL;
p_cost_segment6 VARCHAR2 (50) := NULL;
p_cost_segment7 VARCHAR2 (50) := NULL;
p_balance_segment1 VARCHAR2 (50) := NULL;
p_balance_segment2 VARCHAR2 (50) := NULL;
p_balance_segment3 VARCHAR2 (50) := NULL;
p_balance_segment4 VARCHAR2 (50) := NULL;
p_balance_segment5 VARCHAR2 (50) := NULL;
p_balance_segment6 VARCHAR2 (50) := NULL;
p_balance_segment7 VARCHAR2 (50) := NULL;
--DBMS_OUTPUT.PUT_LINE('AFTER LOCAL VAR -1');
CURSOR c1
IS
(SELECT *
FROM xx_update_link_costing_nol xx
WHERE xx.valid_flag = 'N' AND xx.element_type_id IS NOT NULL)
FOR UPDATE;
v_c1 c1%ROWTYPE;
BEGIN
--INSERT INTO fnd_sessions VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
OPEN c1;
LOOP
FETCH c1
INTO v_c1;
EXIT WHEN c1%NOTFOUND;
FOR i IN (SELECT element_link_id
FROM pay_element_links_f lnk
WHERE lnk.business_group_id = 81
AND lnk.element_type_id = v_c1.element_type_id
AND lnk.payroll_id = v_c1.extra_info1
AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY'))
LOOP
BEGIN
SELECT lnk.effective_start_date, lnk.object_version_number
INTO p_effective_date, p_object_version_number
FROM pay_element_links_f lnk
WHERE lnk.business_group_id = 81
AND lnk.element_link_id = i.element_link_id
AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
BEGIN
p_cost_segment1 := NVL(TO_CHAR(v_c1.cost_segment1, 'fm099999'),'023800');--NVL(v_c1.cost_segment1,'023800');
p_cost_segment2 := NVL(NULL,'000000');
p_cost_segment3 := NVL(TO_CHAR(v_c1.cost_segment3, 'fm0999999'),'000000');
p_cost_segment4 := NVL(NULL,'00');
p_cost_segment5 := NVL(NULL,'0000');
p_cost_segment6 := NVL(NULL,'0000');
p_cost_segment7 := NVL(NULL,'0000');
p_balance_segment1 := NVL(TO_CHAR(v_c1.balance_segment1, 'fm099999'),'023800');--NVL (v_c1.balance_segment1, '023800');
p_balance_segment2 := NVL (NULL, '000000');
p_balance_segment3 := NVL (TO_CHAR(v_c1.balance_segment3, 'fm0999999'), '000000');
p_balance_segment4 := NVL (NULL, '00');
p_balance_segment5 := NVL (NULL, '0000');
p_balance_segment6 := NVL (NULL, '0000');
p_balance_segment7 := NVL (NULL, '0000');
-------------
pay_element_link_api.update_element_link
(p_validate => FALSE,
p_effective_date => p_effective_date,
p_element_link_id => i.element_link_id,
p_datetrack_mode => 'CORRECTION',
p_transfer_to_gl_flag => 'Y',
p_costable_type => 'C',
p_cost_segment1 => p_cost_segment1,
p_cost_segment2 => p_cost_segment2,
p_cost_segment3 => p_cost_segment3,
p_cost_segment4 => p_cost_segment4,
p_cost_segment5 => p_cost_segment5,
p_cost_segment6 => p_cost_segment6,
p_cost_segment7 => p_cost_segment7,
p_balance_segment1 => p_balance_segment1,
p_balance_segment2 => p_balance_segment2,
p_balance_segment3 => p_balance_segment3,
p_balance_segment4 => p_balance_segment4,
p_balance_segment5 => p_balance_segment5,
p_balance_segment6 => p_balance_segment6,
p_balance_segment7 => p_balance_segment7,
p_object_version_number => p_object_version_number,
p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
p_balancing_keyflex_id => p_balancing_keyflex_id,
p_cost_concat_segments_out => p_cost_concat_segments_out,
p_balance_concat_segments_out => p_balance_concat_segments_out,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date
);
UPDATE xx_update_link_costing_nol xx
SET xx.err = 'Done',
xx.valid_flag = 'Y'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER API-1');
EXCEPTION
WHEN OTHERS
THEN
p_cost_segment1 := NULL;
p_cost_segment2 := NULL;
p_cost_segment3 := NULL;
p_cost_segment4 := NULL;
p_cost_segment5 := NULL;
p_cost_segment6 := NULL;
p_cost_segment7 := NULL;
p_balance_segment1 := NULL;
p_balance_segment2 := NULL;
p_balance_segment3 := NULL;
p_balance_segment4 := NULL;
p_balance_segment5 := NULL;
p_balance_segment6 := NULL;
p_balance_segment7 := NULL;
p_effective_date := NULL;
p_object_version_number := NULL;
p_cost_allocation_keyflex_id := NULL;
p_balancing_keyflex_id := NULL;
p_cost_concat_segments_out := NULL;
p_balance_concat_segments_out := NULL;
p_effective_start_date := NULL;
p_effective_end_date := NULL;
v_err := NULL;
v_err := (SQLERRM);
UPDATE xx_update_link_costing_nol xx
SET xx.err = v_err,
xx.valid_flag = 'N'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -1');
END;
EXCEPTION
WHEN OTHERS
THEN
p_cost_segment1 := NULL;
p_cost_segment2 := NULL;
p_cost_segment3 := NULL;
p_cost_segment4 := NULL;
p_cost_segment5 := NULL;
p_cost_segment6 := NULL;
p_cost_segment7 := NULL;
p_balance_segment1 := NULL;
p_balance_segment2 := NULL;
p_balance_segment3 := NULL;
p_balance_segment4 := NULL;
p_balance_segment5 := NULL;
p_balance_segment6 := NULL;
p_balance_segment7 := NULL;
p_effective_date := NULL;
p_object_version_number := NULL;
p_cost_allocation_keyflex_id := NULL;
p_balancing_keyflex_id := NULL;
p_cost_concat_segments_out := NULL;
p_balance_concat_segments_out := NULL;
p_effective_start_date := NULL;
p_effective_end_date := NULL;
v_err := NULL;
UPDATE xx_update_link_costing_nol xx
SET xx.err = 'Element Link Select Error',
xx.valid_flag = 'N'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -2');
END;
END LOOP;
END LOOP;
CLOSE c1;
COMMIT;
END;
--select 1 from dual
Step1: creating staging table XX_UPDATE_LINK_COSTING_NOL.
step2: insert into staging table using select script with required columns.
Step3: run the plsql block or procdure.
1.
CREATE TABLE APPS.XX_UPDATE_LINK_COSTING_NOL
(
ELEMENT_TYPE_ID NUMBER,
COST_SEGMENT1 NUMBER,
COST_SEGMENT2 NUMBER,
COST_SEGMENT3 NUMBER,
COST_SEGMENT4 NUMBER,
COST_SEGMENT5 NUMBER,
COST_SEGMENT6 NUMBER,
COST_SEGMENT7 NUMBER,
BALANCE_SEGMENT1 NUMBER,
BALANCE_SEGMENT2 NUMBER,
BALANCE_SEGMENT3 NUMBER,
BALANCE_SEGMENT4 NUMBER,
BALANCE_SEGMENT5 NUMBER,
BALANCE_SEGMENT6 NUMBER,
BALANCE_SEGMENT7 NUMBER,
VALID_FLAG VARCHAR2(1 BYTE), --Default 'N'
ERR VARCHAR2(2000 BYTE),
EXTRA_INFO1 VARCHAR2(200 BYTE), --Payroll _Id
EXTRA_INFO2 VARCHAR2(200 BYTE),
EXTRA_INFO3 VARCHAR2(200 BYTE)
)
2.
insert into xx_update_link_costing_nol
SELECT element_type_id,
cost.segment1,cost.segment2, cost.segment3, cost.segment4, cost.segment5, cost.segment6, cost.segment7,
balance.segment1,balance.segment2, balance.segment3, balance.segment4, balance.segment5, balance.segment6, balance.segment7,
'N', --default value
null,
61,--Payroll id to which we are going to update
null,
null
FROM
PAY_COST_ALLOCATION_KEYFLEX cost,
PAY_COST_ALLOCATION_KEYFLEX balance,
PAY_ELEMENT_LINKS_F pelf
WHERE
pelf.COST_ALLOCATION_KEYFLEX_ID = cost.COST_ALLOCATION_KEYFLEX_ID
and pelf.BALANCING_KEYFLEX_ID = balance.COST_ALLOCATION_KEYFLEX_ID
and ELEMENT_LINK_ID = 1922--taking only element or testing
COMMIT
3.
CREATE OR REPLACE PROCEDURE P_UPDATE_ELEMENT_ENTRY
AS
--DECLARE
p_effective_date DATE;
v_err VARCHAR2 (1000);
p_object_version_number NUMBER;
p_cost_allocation_keyflex_id NUMBER;
p_balancing_keyflex_id NUMBER;
p_cost_concat_segments_out VARCHAR2 (250);
p_balance_concat_segments_out VARCHAR2 (250);
p_effective_start_date DATE;
p_effective_end_date DATE;
-------------------------------
p_cost_segment1 VARCHAR2 (50) := NULL;
p_cost_segment2 VARCHAR2 (50) := NULL;
p_cost_segment3 VARCHAR2 (50) := NULL;
p_cost_segment4 VARCHAR2 (50) := NULL;
p_cost_segment5 VARCHAR2 (50) := NULL;
p_cost_segment6 VARCHAR2 (50) := NULL;
p_cost_segment7 VARCHAR2 (50) := NULL;
p_balance_segment1 VARCHAR2 (50) := NULL;
p_balance_segment2 VARCHAR2 (50) := NULL;
p_balance_segment3 VARCHAR2 (50) := NULL;
p_balance_segment4 VARCHAR2 (50) := NULL;
p_balance_segment5 VARCHAR2 (50) := NULL;
p_balance_segment6 VARCHAR2 (50) := NULL;
p_balance_segment7 VARCHAR2 (50) := NULL;
--DBMS_OUTPUT.PUT_LINE('AFTER LOCAL VAR -1');
CURSOR c1
IS
(SELECT *
FROM xx_update_link_costing_nol xx
WHERE xx.valid_flag = 'N' AND xx.element_type_id IS NOT NULL)
FOR UPDATE;
v_c1 c1%ROWTYPE;
BEGIN
--INSERT INTO fnd_sessions VALUES (USERENV ('SESSIONID'), TRUNC (SYSDATE));
OPEN c1;
LOOP
FETCH c1
INTO v_c1;
EXIT WHEN c1%NOTFOUND;
FOR i IN (SELECT element_link_id
FROM pay_element_links_f lnk
WHERE lnk.business_group_id = 81
AND lnk.element_type_id = v_c1.element_type_id
AND lnk.payroll_id = v_c1.extra_info1
AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY'))
LOOP
BEGIN
SELECT lnk.effective_start_date, lnk.object_version_number
INTO p_effective_date, p_object_version_number
FROM pay_element_links_f lnk
WHERE lnk.business_group_id = 81
AND lnk.element_link_id = i.element_link_id
AND lnk.effective_end_date = TO_DATE ('31/12/4712', 'DD/MM/YYYY');
BEGIN
p_cost_segment1 := NVL(TO_CHAR(v_c1.cost_segment1, 'fm099999'),'023800');--NVL(v_c1.cost_segment1,'023800');
p_cost_segment2 := NVL(NULL,'000000');
p_cost_segment3 := NVL(TO_CHAR(v_c1.cost_segment3, 'fm0999999'),'000000');
p_cost_segment4 := NVL(NULL,'00');
p_cost_segment5 := NVL(NULL,'0000');
p_cost_segment6 := NVL(NULL,'0000');
p_cost_segment7 := NVL(NULL,'0000');
p_balance_segment1 := NVL(TO_CHAR(v_c1.balance_segment1, 'fm099999'),'023800');--NVL (v_c1.balance_segment1, '023800');
p_balance_segment2 := NVL (NULL, '000000');
p_balance_segment3 := NVL (TO_CHAR(v_c1.balance_segment3, 'fm0999999'), '000000');
p_balance_segment4 := NVL (NULL, '00');
p_balance_segment5 := NVL (NULL, '0000');
p_balance_segment6 := NVL (NULL, '0000');
p_balance_segment7 := NVL (NULL, '0000');
-------------
pay_element_link_api.update_element_link
(p_validate => FALSE,
p_effective_date => p_effective_date,
p_element_link_id => i.element_link_id,
p_datetrack_mode => 'CORRECTION',
p_transfer_to_gl_flag => 'Y',
p_costable_type => 'C',
p_cost_segment1 => p_cost_segment1,
p_cost_segment2 => p_cost_segment2,
p_cost_segment3 => p_cost_segment3,
p_cost_segment4 => p_cost_segment4,
p_cost_segment5 => p_cost_segment5,
p_cost_segment6 => p_cost_segment6,
p_cost_segment7 => p_cost_segment7,
p_balance_segment1 => p_balance_segment1,
p_balance_segment2 => p_balance_segment2,
p_balance_segment3 => p_balance_segment3,
p_balance_segment4 => p_balance_segment4,
p_balance_segment5 => p_balance_segment5,
p_balance_segment6 => p_balance_segment6,
p_balance_segment7 => p_balance_segment7,
p_object_version_number => p_object_version_number,
p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
p_balancing_keyflex_id => p_balancing_keyflex_id,
p_cost_concat_segments_out => p_cost_concat_segments_out,
p_balance_concat_segments_out => p_balance_concat_segments_out,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date
);
UPDATE xx_update_link_costing_nol xx
SET xx.err = 'Done',
xx.valid_flag = 'Y'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER API-1');
EXCEPTION
WHEN OTHERS
THEN
p_cost_segment1 := NULL;
p_cost_segment2 := NULL;
p_cost_segment3 := NULL;
p_cost_segment4 := NULL;
p_cost_segment5 := NULL;
p_cost_segment6 := NULL;
p_cost_segment7 := NULL;
p_balance_segment1 := NULL;
p_balance_segment2 := NULL;
p_balance_segment3 := NULL;
p_balance_segment4 := NULL;
p_balance_segment5 := NULL;
p_balance_segment6 := NULL;
p_balance_segment7 := NULL;
p_effective_date := NULL;
p_object_version_number := NULL;
p_cost_allocation_keyflex_id := NULL;
p_balancing_keyflex_id := NULL;
p_cost_concat_segments_out := NULL;
p_balance_concat_segments_out := NULL;
p_effective_start_date := NULL;
p_effective_end_date := NULL;
v_err := NULL;
v_err := (SQLERRM);
UPDATE xx_update_link_costing_nol xx
SET xx.err = v_err,
xx.valid_flag = 'N'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -1');
END;
EXCEPTION
WHEN OTHERS
THEN
p_cost_segment1 := NULL;
p_cost_segment2 := NULL;
p_cost_segment3 := NULL;
p_cost_segment4 := NULL;
p_cost_segment5 := NULL;
p_cost_segment6 := NULL;
p_cost_segment7 := NULL;
p_balance_segment1 := NULL;
p_balance_segment2 := NULL;
p_balance_segment3 := NULL;
p_balance_segment4 := NULL;
p_balance_segment5 := NULL;
p_balance_segment6 := NULL;
p_balance_segment7 := NULL;
p_effective_date := NULL;
p_object_version_number := NULL;
p_cost_allocation_keyflex_id := NULL;
p_balancing_keyflex_id := NULL;
p_cost_concat_segments_out := NULL;
p_balance_concat_segments_out := NULL;
p_effective_start_date := NULL;
p_effective_end_date := NULL;
v_err := NULL;
UPDATE xx_update_link_costing_nol xx
SET xx.err = 'Element Link Select Error',
xx.valid_flag = 'N'
WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE('UNDER EXCEPTION -2');
END;
END LOOP;
END LOOP;
CLOSE c1;
COMMIT;
END;
--select 1 from dual
--------------------------------------------------------------------------------------------------------------------------
Known issues:
check the segments 6 or 7.
sample below script to update 1 element.
--------------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR c1
IS
SELECT pcak_bal.concatenated_segments bal,
pcak_cost.concatenated_segments cost,
pcak_cost.segment1 cost_seg1,
pcak_cost.segment2 cost_seg2,
pcak_cost.segment3 cost_seg3,
pcak_cost.segment4 cost_seg4,
pcak_cost.segment5 cost_seg5,
pcak_cost.segment6 cost_seg6,
pcak_cost.segment7 cost_seg7,
pcak_bal.segment1 bal_seg1,
pcak_bal.segment2 bal_seg2,
pcak_bal.segment3 bal_seg3,
pcak_bal.segment4 bal_seg4,
pcak_bal.segment5 bal_seg5,
pcak_bal.segment6 bal_seg6,
pcak_bal.segment7 bal_seg7,
pelf.*
FROM pay_element_links_f pelf,
pay_cost_allocation_keyflex pcak_cost,
pay_cost_allocation_keyflex pcak_bal
WHERE business_group_id = 81
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date
AND pelf.cost_allocation_keyflex_id =
pcak_cost.cost_allocation_keyflex_id
AND pelf.balancing_keyflex_id =
pcak_bal.cost_allocation_keyflex_id
AND PELF.PAYROLL_ID = 141
AND PELF.ELEMENT_TYPE_ID NOT IN( 161)
;
-- AND pcak_bal.segment6 = '843000'
l_datetrack_mode VARCHAR2 (100) := 'CORRECTION';
l_bal_segment6 VARCHAR2 (100) := '000000';
l_cost_allocation_keyflex_id NUMBER := NULL;
l_balancing_keyflex_id NUMBER := NULL;
l_cost_concat_segments_out VARCHAR2 (500) := NULL;
l_balance_concat_segments_out VARCHAR2 (500) := NULL;
l_effective_start_date DATE := NULL;
l_effective_end_date DATE := NULL;
BEGIN
FOR i IN c1
LOOP
BEGIN
l_cost_allocation_keyflex_id := NULL;
l_balancing_keyflex_id := NULL;
l_cost_concat_segments_out := NULL;
l_balance_concat_segments_out := NULL;
l_effective_start_date := NULL;
l_effective_end_date := NULL;
pay_element_link_api.update_element_link (
p_validate => FALSE,
p_effective_date => i.effective_start_date,
p_element_link_id => i.element_link_id,
p_datetrack_mode => l_datetrack_mode,
p_transfer_to_gl_flag => 'Y',
p_costable_type => 'C',
p_cost_segment1 => i.cost_seg1,
p_cost_segment2 => i.cost_seg2,
p_cost_segment3 => i.cost_seg3,
p_cost_segment4 => i.cost_seg4,
p_cost_segment5 => i.cost_seg5,
p_cost_segment6 => i.cost_seg6,
-- p_cost_segment7 => i.cost_seg7,
p_balance_segment1 => '006',
p_balance_segment2 => i.bal_seg2,
p_balance_segment3 => i.bal_seg3,
p_balance_segment4 => i.bal_seg4,
p_balance_segment5 => i.bal_seg5,
p_balance_segment6 => i.bal_seg6,
-- p_balance_segment7 => i.bal_seg7,
p_object_version_number => i.object_version_number,
p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id,
p_balancing_keyflex_id => l_balancing_keyflex_id,
p_cost_concat_segments_out => l_cost_concat_segments_out,
p_balance_concat_segments_out => l_balance_concat_segments_out,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date);
COMMIT;
DBMS_OUTPUT.put_line (
i.element_link_id || ' has been Updated --> ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Failure - ' || i.element_link_id || '-->' || SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception ' || SQLERRM);
END;
No comments:
Post a Comment