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
No comments:
Post a Comment