Execute On your Own Risk !!!!
SELECT V.PROFILE_OPTION_VALUE OE_PROFILE
FROM FND_PROFILE_OPTION_VALUES V
WHERE (V.PROFILE_OPTION_ID, V.APPLICATION_ID, V.LEVEL_ID) =
(SELECT V2.PROFILE_OPTION_ID,V2.APPLICATION_ID,MAX(V2.LEVEL_ID)
FROM FND_PROFILE_OPTIONS OO,
FND_PROFILE_OPTION_VALUES V2
WHERE ((V2.LEVEL_ID = 10001 AND V2.LEVEL_VALUE=0)
OR (V2.LEVEL_ID = 10002 AND V2.LEVEL_VALUE=660))
AND OO.PROFILE_OPTION_ID = V2.PROFILE_OPTION_ID
AND OO.APPLICATION_ID = V2.APPLICATION_ID
AND OO.APPLICATION_ID = 660
AND UPPER(OO.PROFILE_OPTION_NAME) = 'ONT_SOURCE_CODE'
GROUP BY V2.PROFILE_OPTION_ID, V2.APPLICATION_ID)
AND V.LEVEL_VALUE = DECODE(V.LEVEL_ID, 10001, 0, 10002, 660);
PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been cancelled
SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE');
UPDATE MTL_RESERVATIONS
SET PRIMARY_RESERVATION_QUANTITY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE PRIMARY_RESERVATION_QUANTITY>0
AND EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL L
WHERE nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE'));
PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been closed or delivery detail is cancelled
SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D');
UPDATE MTL_RESERVATIONS
SET PRIMARY_RESERVATION_QUANTITY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE PRIMARY_RESERVATION_QUANTITY>0
AND EXISTS (SELECT 'X'
FROM OE_ORDER_LINES_ALL L
WHERE nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = nvl(MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID,-99)
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
AND MTI.SOURCE_HEADER_ID = L.HEADER_ID
AND MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
AND WDD.SOURCE_CODE ='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P')
AND WDD.RELEASED_STATUS <> 'D'));
PROMPT ORDER DETAILS WITH ORPHAN RESERVATIONS AFTER LINE IS DELETED
SELECT MSO.SEGMENT1 ORD_NUMBER,
MSO.SEGMENT2 ORD_TYPE,
MSO.SALES_ORDER_ID sALES_ORDER_ID,
MR.DEMAND_SOURCE_LINE_ID oRDER_LINE_ID,
MR.iNVENTORY_ITEM_ID iTEM_ID,
MR.ORGANIZATION_ID ORGANIZATION_ID,
MR.PRIMARY_RESERVATION_QUANTITY pRSV_QTY,
MR.RESERVATION_QUANTITY RSV_QTY
FROM MTL_RESERVATIONS MR,
MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID=MR.DEMAND_SOURCE_HEADER_ID
AND MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID );
PROMPT UPDATING MTL_RESERVATIONS
UPDATE MTL_RESERVATIONS MR
SET PRIMARY_RESERVATION_QUANTITY=0,
RESERVATION_QUANTITY=0,
LAST_UPDATED_BY=-2471362
WHERE MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID);
COMMIT;
PROMPT **Updating records for closed /cancelled lines in mtl_demand where records are not in sync
UPDATE MTL_DEMAND D
SET PRIMARY_UOM_QUANTITY = 0
, LINE_ITEM_QUANTITY = 0
, COMPLETED_QUANTITY = 0
, LINE_ITEM_RESERVATION_QTY = 0
, RESERVATION_QUANTITY = 0
, LAST_UPDATED_BY=-2471362
WHERE DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE = 2
AND DEMAND_SOURCE_LINE = ( SELECT O.LINE_ID
FROM OE_ORDER_LINES_ALL O
WHERE ( NVL(o.OPEN_FLAG,'Y') = 'N'
OR NVL(O.CANCELLED_FLAG,'N') ='Y')
AND O.LINE_ID =D.DEMAND_SOURCE_LINE )
AND DEMAND_SOURCE_LINE NOT IN ( select TRX_SOURCE_LINE_ID
FROM MTL_TRANSACTIONS_INTERFACE MTI
WHERE MTI.SOURCE_LINE_ID = D.DEMAND_SOURCE_LINE)
AND DEMAND_SOURCE_LINE NOT IN (SELECT SOURCE_LINE_ID
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_LINE_ID=D.DEMAND_SOURCE_LINE
AND WDD.SOURCE_CODE='OE'
AND WDD.INV_INTERFACED_FLAG IN ('N','P'));
PROMPT FINAL UPDATING MTL_DEMAND
UPDATE MTL_DEMAND SET PRIMARY_UOM_QUANTITY=0,
COMPLETED_QUANTITY=0,
RESERVATION_QUANTITY=0,
LAST_UPDATED_BY=-2471362
WHERE DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2
AND DEMAND_ID IN (SELECT N_COLUMN1 FROM MTL_RESERVATIONS WHERE
DEMAND_SOURCE_TYPE_ID IN (2,8)
AND PRIMARY_RESERVATION_QUANTITY=0
AND RESERVATION_QUANTITY=0
AND LAST_UPDATED_BY=-2471362);
PROMPT **Deleting reservations which are complete
COMMIT;
DELETE FROM MTL_RESERVATIONS WHERE
DEMAND_SOURCE_TYPE_ID IN (2,8)
AND LAST_UPDATED_BY=-2471362;
PROMPT **Deleting records from mtl_demand where reservations are complete
DELETE FROM MTL_DEMAND WHERE
DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2
AND LAST_UPDATED_BY=-2471362;
spool off
COMMIT;
EXIT;
Creating a blog is not just a hobby it’s a partnership to grow together.
Sunday, August 21, 2011
Monday, August 8, 2011
Inventory Exception Report - ( SQL*Plus- Fancy HTML )
/* Fancy HTML Inventory Exception Report - SQL*Plus for Missing Subinventory Assignments */
SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF;
SET PAGESIZE 200;
SET FEEDBACK OFF;
SET MARKUP HTML ENTMAP OFF;
DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);
CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = '&1'
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE
CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = '&1'
AND SECONDARY_INVENTORY_NAME IN ('ELECS-HA', 'STAGING');
BEGIN
-- fnd_file.put_line (fnd_file.LOG,'RUSHIKESH Log ');
-- FND_FILE.put_line(FND_FILE.output,'RUSHIKESH Output');
DBMS_OUTPUT.PUT_LINE('Report Run Date : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('
' );
END;
/
SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF;
SET PAGESIZE 200;
SET FEEDBACK OFF;
SET MARKUP HTML ENTMAP OFF;
DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);
CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = '&1'
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE
CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = '&1'
AND SECONDARY_INVENTORY_NAME IN ('ELECS-HA', 'STAGING');
BEGIN
-- fnd_file.put_line (fnd_file.LOG,'RUSHIKESH Log ');
-- FND_FILE.put_line(FND_FILE.output,'RUSHIKESH Output');
DBMS_OUTPUT.PUT_LINE('Report Run Date : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('
| Item Code | Organization | Missing Subinventory |
|---|---|---|
| '|| M_ITEM ||' | ' || '' || M_ORG_CODE ||' | '
|| '' || R1.SECONDARY_INVENTORY_NAME ||' |
END;
/
Sunday, August 7, 2011
Item Defined But Sub inventory Not Assigned in Organization
DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);
CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = 122 -- organization wise
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE
CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = 122
AND SECONDARY_INVENTORY_NAME IN ('EL-HA', 'STAGING');
-- Subinventories defined for organization
BEGIN
DBMS_OUTPUT.PUT_LINE('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
L_ITEM_COUNT := 0;
FOR R IN C
LOOP
L_ITEM_ID := NULL;
L_ORGANIZATION_ID := NULL;
IF (L_ERR_FLAG <> 'Y')
THEN
FOR R1 IN SUB_INVS (R.ORGANIZATION_ID)
LOOP
BEGIN
-- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING --
SELECT 'm'
INTO M_EXISTS
FROM APPS.MTL_ITEM_SUB_INVENTORIES
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID
AND SECONDARY_INVENTORY = R1.SECONDARY_INVENTORY_NAME;
--DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID :'||R.INVENTORY_ITEM_ID);
--DBMS_OUTPUT.PUT_LINE('ORGANIZATION_ID :'||R.ORGANIZATION_ID);
--DBMS_OUTPUT.PUT_LINE('SUB INVENTORY :'||R1.SECONDARY_INVENTORY_NAME);
----- IF THE COMBINATION DOESNOT EXIST THEN POPULATE TO THE SUBINVENTORY TABLE ----------------
SELECT SEGMENT1
INTO M_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID;
SELECT ORGANIZATION_CODE
INTO M_ORG_CODE
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = R.ORGANIZATION_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE( M_ITEM
|| '-'
|| M_ORG_CODE
|| '-'
|| R1.SECONDARY_INVENTORY_NAME);
/* Commented to Verify first
INSERT INTO APPS.MTL_ITEM_SUB_INVENTORIES (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_PLANNING_CODE
)
VALUES (R.INVENTORY_ITEM_ID,
R.ORGANIZATION_ID,
R1.SECONDARY_INVENTORY_NAME,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);
COMMIT;
*/
L_ITEM_COUNT := L_ITEM_COUNT + 1;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;
END;
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);
CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = 122 -- organization wise
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE
CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = 122
AND SECONDARY_INVENTORY_NAME IN ('EL-HA', 'STAGING');
-- Subinventories defined for organization
BEGIN
DBMS_OUTPUT.PUT_LINE('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
L_ITEM_COUNT := 0;
FOR R IN C
LOOP
L_ITEM_ID := NULL;
L_ORGANIZATION_ID := NULL;
IF (L_ERR_FLAG <> 'Y')
THEN
FOR R1 IN SUB_INVS (R.ORGANIZATION_ID)
LOOP
BEGIN
-- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING --
SELECT 'm'
INTO M_EXISTS
FROM APPS.MTL_ITEM_SUB_INVENTORIES
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID
AND SECONDARY_INVENTORY = R1.SECONDARY_INVENTORY_NAME;
--DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID :'||R.INVENTORY_ITEM_ID);
--DBMS_OUTPUT.PUT_LINE('ORGANIZATION_ID :'||R.ORGANIZATION_ID);
--DBMS_OUTPUT.PUT_LINE('SUB INVENTORY :'||R1.SECONDARY_INVENTORY_NAME);
----- IF THE COMBINATION DOESNOT EXIST THEN POPULATE TO THE SUBINVENTORY TABLE ----------------
SELECT SEGMENT1
INTO M_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = R.ORGANIZATION_ID;
SELECT ORGANIZATION_CODE
INTO M_ORG_CODE
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_ID = R.ORGANIZATION_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE( M_ITEM
|| '-'
|| M_ORG_CODE
|| '-'
|| R1.SECONDARY_INVENTORY_NAME);
/* Commented to Verify first
INSERT INTO APPS.MTL_ITEM_SUB_INVENTORIES (
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_PLANNING_CODE
)
VALUES (R.INVENTORY_ITEM_ID,
R.ORGANIZATION_ID,
R1.SECONDARY_INVENTORY_NAME,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);
COMMIT;
*/
L_ITEM_COUNT := L_ITEM_COUNT + 1;
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;
END;
Tuesday, August 2, 2011
Where's My Journal Voucher JV Stuck ?
--------------------------------- Solution I -------------------------------------------
In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:
1. Verify the Workflow process is not there
This can be verified in Workflow (monitor):
Workflow - Find Processes
Item Type = Journal Batch
Process Name = GL Journal Approval Process
Or:
select *
from wf_notifications b
where b.subject like '%ournal batch%' -- Journal Batch Name
or begin_date like to_date ('09082005','dd/mm/yyyy')
or b.recipient_role like '%Approver_name%'
Resolution could be one of the following solutions:-
1. Approve the batch:-
update gl_je_batches
set approval_status_code = 'J'
where je_batch_id = &batch_id
To automatically approve the batch, set the approval_status_code in GL_JE_BATCHES to 'A'. This
will make the batch immediately available for posting.
or
2. To send the batch back for approval through workflow, set the @approval_status_code to 'R'.
--------------------------------- Solution II -------------------------------------------
In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:
Select the 'Workflow Administrator responsibility'.
Note that the Workflow Administrator Role must be assigned to the User or Responsibility that is doing this.
Open the 'Status Monitor' screen (Administrator Workflow > Status Monitor)
---------
Query the workflow in the current context. To know the parameter values to use,
(Journal Approval uses the following values:
the item_type is 'GLBATCH'.
the item_key value starts with the JE_BATCH_ID value from gl_je_batches table.
the user_key is the Journal Batch Name (this is case sensitive).
Use the following SQL to obtain the item_key and item_type for a Journal Batch
Approval workflow:
SELECT item_type, item_key
FROM wf_items
WHERE user_key = '';
The tables where you can find relevant data using the above references are:
GL_JE_BATCHES
WF_NOTIFICATIONS
WF_ITEMS
WF_ITEM_ACTIVITY_STATUSES
WF_PROCESS_ACTIVITIES
)
Select the workflow with error from the list and open the 'Activity History'.
---------
Select the failed activity and click the 'Update Attributes' button.
---------
Update the missing attributes of the failed activity, for example:
- preparer id = employee id from the fnd_user table (-1 shows that an assignment was missing)
- Approver Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)
- Approver Display Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)
---------
Select the errored out activity and click on the 'Rewind' button.
Clicking on rewind button opens a page listing all the completed activities of this workflow and gives you a choice to select the activity from where the workflow should be rewound.
Select the first activity of the workflow as the point from where it is to be rewound.
A confirmation was received that the workflow was rewound .
Requery the workflow to see the latest status.
The notification must have been sent to the approver.
In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:
1. Verify the Workflow process is not there
This can be verified in Workflow (monitor):
Workflow - Find Processes
Item Type = Journal Batch
Process Name = GL Journal Approval Process
Or:
select *
from wf_notifications b
where b.subject like '%ournal batch%' -- Journal Batch Name
or begin_date like to_date ('09082005','dd/mm/yyyy')
or b.recipient_role like '%Approver_name%'
Resolution could be one of the following solutions:-
1. Approve the batch:-
update gl_je_batches
set approval_status_code = 'J'
where je_batch_id = &batch_id
To automatically approve the batch, set the approval_status_code in GL_JE_BATCHES to 'A'. This
will make the batch immediately available for posting.
or
2. To send the batch back for approval through workflow, set the @approval_status_code to 'R'.
--------------------------------- Solution II -------------------------------------------
In this example, the workflow failed originally because the journal preparers supervisor did not have an employee name assigned to the user.
Depending on the case, the corrected data must be entered in the steps below:
Select the 'Workflow Administrator responsibility'.
Note that the Workflow Administrator Role must be assigned to the User or Responsibility that is doing this.
Open the 'Status Monitor' screen (Administrator Workflow > Status Monitor)
---------
Query the workflow in the current context. To know the parameter values to use,
(Journal Approval uses the following values:
the item_type is 'GLBATCH'.
the item_key value starts with the JE_BATCH_ID value from gl_je_batches table.
the user_key is the Journal Batch Name (this is case sensitive).
Use the following SQL to obtain the item_key and item_type for a Journal Batch
Approval workflow:
SELECT item_type, item_key
FROM wf_items
WHERE user_key = '
The tables where you can find relevant data using the above references are:
GL_JE_BATCHES
WF_NOTIFICATIONS
WF_ITEMS
WF_ITEM_ACTIVITY_STATUSES
WF_PROCESS_ACTIVITIES
)
Select the workflow with error from the list and open the 'Activity History'.
---------
Select the failed activity and click the 'Update Attributes' button.
---------
Update the missing attributes of the failed activity, for example:
- preparer id = employee id from the fnd_user table (-1 shows that an assignment was missing)
- Approver Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)
- Approver Display Name = Preparer's supervisor's name (i.e.Person Name to be Attached for the supervisor User)
---------
Select the errored out activity and click on the 'Rewind' button.
Clicking on rewind button opens a page listing all the completed activities of this workflow and gives you a choice to select the activity from where the workflow should be rewound.
Select the first activity of the workflow as the point from where it is to be rewound.
A confirmation was received that the workflow was rewound .
Requery the workflow to see the latest status.
The notification must have been sent to the approver.
Payroll GL Account wise Break Up - All Accounts
/* Payroll - Break Up for Different Accounts can be generated from following SQL */
SELECT FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT
FROM (SELECT PAP.FULL_NAME,
PAP.EMPLOYEE_NUMBER,
V.RESULT_VALUE,
E.REPORTING_NAME,
HORG.NAME,
PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS,
PC.COSTED_VALUE,
PC.BALANCE_OR_COST,
PC.DEBIT_OR_CREDIT
FROM APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID = R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID = A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID = P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ('31-JUL-11' BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID = PAP.PERSON_ID
AND ('31-JUL-11' BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE = '31-JUL-11'
AND PCAK.SEGMENT5 = :Account_code --51203
AND PC.COST_ALLOCATION_KEYFLEX_ID =
PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND P.PAYROLL_ID IN (01, 02, 03)) -- will change as per payroll id
WHERE RESULT_VALUE IS NOT NULL
--AND EMPLOYEE_NUMBER LIKE '5%'
GROUP BY FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT
SELECT FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT
FROM (SELECT PAP.FULL_NAME,
PAP.EMPLOYEE_NUMBER,
V.RESULT_VALUE,
E.REPORTING_NAME,
HORG.NAME,
PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS,
PC.COSTED_VALUE,
PC.BALANCE_OR_COST,
PC.DEBIT_OR_CREDIT
FROM APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID = E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID = R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID = A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID = P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ('31-JUL-11' BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID = PAP.PERSON_ID
AND ('31-JUL-11' BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE = '31-JUL-11'
AND PCAK.SEGMENT5 = :Account_code --51203
AND PC.COST_ALLOCATION_KEYFLEX_ID =
PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND P.PAYROLL_ID IN (01, 02, 03)) -- will change as per payroll id
WHERE RESULT_VALUE IS NOT NULL
--AND EMPLOYEE_NUMBER LIKE '5%'
GROUP BY FULL_NAME,
EMPLOYEE_NUMBER,
RESULT_VALUE,
REPORTING_NAME,
NAME,
COST_SEGMENTS,
COSTED_VALUE,
DEBIT_OR_CREDIT
Payroll GL Account wise Break Up
/* Payroll GL Account wise Break Up */
/* You have a GL account for payrole , one can sue this query to find out break up for accounts like staff deduction etc */
SELECT FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT
FROM
(
SELECT PAP.FULL_NAME,PAP.EMPLOYEE_NUMBER,V.RESULT_VALUE,
E.REPORTING_NAME,HORG.NAME, PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS, PC.COSTED_VALUE, PC.BALANCE_OR_COST, PC.DEBIT_OR_CREDIT
FROM
APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID=E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID=R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID=A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID=P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID=PAP.PERSON_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE= :P_EFFECTIVE_DATE
AND PCAK.SEGMENT5 = :P_ACCOUNT_NO
AND PC.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND p.PAYROLL_ID = 63 -- will chage as per your company setup
)
WHERE RESULT_VALUE IS NOT NULL
GROUP BY FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT
/* You have a GL account for payrole , one can sue this query to find out break up for accounts like staff deduction etc */
SELECT FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT
FROM
(
SELECT PAP.FULL_NAME,PAP.EMPLOYEE_NUMBER,V.RESULT_VALUE,
E.REPORTING_NAME,HORG.NAME, PCAK.CONCATENATED_SEGMENTS COST_SEGMENTS, PC.COSTED_VALUE, PC.BALANCE_OR_COST, PC.DEBIT_OR_CREDIT
FROM
APPS.PAY_RUN_RESULT_VALUES V,
APPS.PAY_RUN_RESULTS R,
APPS.PAY_ELEMENT_TYPES_F E,
APPS.PAY_ASSIGNMENT_ACTIONS A,
APPS.pay_payroll_actions P,
APPS.HR_ALL_ORGANIZATION_UNITS HORG,
APPS.per_all_assignments_f PAA,
APPS.PAY_COST_ALLOCATION_KEYFLEX PCAK,
APPS.PAY_COSTS PC,
APPS.per_all_people_f PAP,
APPS.PAY_INPUT_VALUES_F PIV
WHERE R.ELEMENT_TYPE_ID=E.ELEMENT_TYPE_ID
AND V.RUN_RESULT_ID=R.RUN_RESULT_ID
--AND R.SOURCE_TYPE<>'E'
AND R.ASSIGNMENT_ACTION_ID=A.ASSIGNMENT_ACTION_ID
AND A.PAYROLL_ACTION_ID=P.PAYROLL_ACTION_ID
AND A.ASSIGNMENT_ID=PAA.ASSIGNMENT_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE AND PAA.EFFECTIVE_END_DATE)
AND PAA.PERSON_ID=PAP.PERSON_ID
AND (:P_EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE)
AND PAA.ORGANIZATION_ID = HORG.ORGANIZATION_ID
AND P.EFFECTIVE_DATE= :P_EFFECTIVE_DATE
AND PCAK.SEGMENT5 = :P_ACCOUNT_NO
AND PC.COST_ALLOCATION_KEYFLEX_ID = PCAK.COST_ALLOCATION_KEYFLEX_ID
AND R.RUN_RESULT_ID = PC.RUN_RESULT_ID
AND V.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.UOM = 'M'
AND p.PAYROLL_ID = 63 -- will chage as per your company setup
)
WHERE RESULT_VALUE IS NOT NULL
GROUP BY FULL_NAME,EMPLOYEE_NUMBER,RESULT_VALUE,REPORTING_NAME,NAME, COST_SEGMENTS,COSTED_VALUE,DEBIT_OR_CREDIT
R12 Customer Query
/* R12 Customer Query */
SELECT DISTINCT *
FROM HZ_PARTIES CUST,
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_CUST_ACCT_SITES_ALL CUST_SITE,
HZ_CUST_SITE_USES_ALL CUST_USES,
HZ_LOCATIONS CUST_LOC
WHERE CUST_ACCT.CUST_ACCOUNT_ID = CUST_SITE.CUST_ACCOUNT_ID
AND CUST_SITE.CUST_ACCT_SITE_ID = CUST_USES.CUST_ACCT_SITE_ID
--AND CUST_USES.SITE_USE_ID = EXT_PAYER.ACCT_SITE_USE_ID
AND CUST_USES.LOCATION = CUST_LOC.LOCATION_ID(+)
AND CUST.PARTY_ID = CUST_ACCT.PARTY_ID
--AND PARTY_NAME LIKE '2%'
AND CUST_ACCT.CUST_ACCOUNT_ID = 20657 --(OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID)
SELECT DISTINCT *
FROM HZ_PARTIES CUST,
HZ_CUST_ACCOUNTS CUST_ACCT,
HZ_CUST_ACCT_SITES_ALL CUST_SITE,
HZ_CUST_SITE_USES_ALL CUST_USES,
HZ_LOCATIONS CUST_LOC
WHERE CUST_ACCT.CUST_ACCOUNT_ID = CUST_SITE.CUST_ACCOUNT_ID
AND CUST_SITE.CUST_ACCT_SITE_ID = CUST_USES.CUST_ACCT_SITE_ID
--AND CUST_USES.SITE_USE_ID = EXT_PAYER.ACCT_SITE_USE_ID
AND CUST_USES.LOCATION = CUST_LOC.LOCATION_ID(+)
AND CUST.PARTY_ID = CUST_ACCT.PARTY_ID
--AND PARTY_NAME LIKE '2%'
AND CUST_ACCT.CUST_ACCOUNT_ID = 20657 --(OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID)
Inventory Opening Balance Interface (On Hand Qty Interface) Continue to Item Create
--- Create a Temp Table
-- Upload data from user xls to temp table
-- run a cursor to plae data from temp table
REM +=======================================================================+
REM | |
REM | |
REM +=======================================================================+
REM | FILENAME |
REM | |
REM | |
REM | DESCRIPTION |
REM | This script has been created to Uplaod Inventory opening balances |
REM | from legacy system to Oracle 11i/R12 inventory |
REM | |
REM | 1. Update User XLS data into an TEMP table |
REM | 2. Run a cursor to move data from temp table to pre int table |
REM | 3. Run a plsql block to migrate data from |
REM | pre_interface_table to oracle std interface table |
REM | 4. Launch Transaction manager from Inventory |
REM +=======================================================================+
CREATE TABLE APPS.AEO_ITM_LOAD_TMP
(
PART_CODE VARCHAR2(240 BYTE),
DIVISION VARCHAR2(3 BYTE),
PARTS_NAME VARCHAR2(240 BYTE),
STOCK_RECD NUMBER,
BALANCE NUMBER,
ISSUE_DATE DATE,
UNIT_PRICE NUMBER,
BRAND VARCHAR2(240 BYTE),
PRODUCT VARCHAR2(240 BYTE),
MODEL_NO VARCHAR2(240 BYTE)
)
COMMIT;
-- Onhand 22 @ rate of 50 , and sysdate-50 is last issue date
INSERT INTO AEO_ITM_LOAD_TMP
VALUES ('RARDE00093',
777,
'RAR Switch - Ignition',
22,
22,
SYSDATE - 50,
50,
'Daewoo ',
NULL,
NULL)
SELECT * FROM APPS.AEO_ITM_LOAD_TMP
------------------- Upload XLS data in Above created Table ------------------------
-- Create a pre interface table
CREATE TABLE UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)
DELETE FROM UETDT.UET_INV_OPENING_BALANCES_INT
COMMIT;
-- Move data from TMP table to pre interface table with validations
DECLARE
N NUMBER;
CURSOR C1
IS
SELECT * FROM AEO_ITM_LOAD_TMP;
BEGIN
FOR REC IN C1
LOOP
INSERT INTO UETDT.UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (REC.DIVISION,
426, ----- INVENTORY_ORG_CODE
'ELECTRONIC',
REC.PART_CODE,
'PCE',
REC.BALANCE,
REC.UNIT_PRICE,
REC.BALANCE * REC.UNIT_PRICE,
'SPARES',
NULL,
NULL,
REC.ISSUE_DATE,
REC.ISSUE_DATE,
REC.BRAND,
'N');
COMMIT;
dbms_output.put_line('Started populating Inventory Opening Balances at TEMP Table : '|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
END LOOP;
END;
SELECT * FROM UETDT.UET_INV_OPENING_BALANCES_INT
------------------------ Populating Inventory Opening Balances -------------------------------
-- Material acount is Receipt account not COGS
DECLARE
L_TRANSACTION_INTERFACE_ID NUMBER;
L_ITEM_COUNT NUMBER := 0;
L_EXP_COUNT NUMBER := 0;
CURSOR ITEM_C
IS
SELECT X.ITEM_CODE OLD_ITEM_CODE, X.QTY QUANTITY, X.UNIT_COST COST, X.LAST_ISSUE_DATE,
X.LAST_RECEIPT_DATE, X.LAST_SUPPLIER_NO, X.ITEM_CODE, X.EXPIRY_DATE LAST_EXP_DATE,
X.VALUE, MSI.ORGANIZATION_ID, MSI.RESTRICT_LOCATORS_CODE,
MSIF.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
GET_LOCATOR_ID(MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, X.STOCK_LOCATOR) LOCATOR_ID,
53322 MATERIAL_ACCOUNT, MSI.PRIMARY_UOM_CODE, MSI.INVENTORY_ITEM_ID,
P.DEFAULT_COST_GROUP_ID COST_GROUP_ID
FROM UETDT.UET_INV_OPENING_BALANCES_INT X,
MTL_SYSTEM_ITEMS MSI,
MTL_SECONDARY_INVENTORIES_FK_V MSIF,
MTL_PARAMETERS P
WHERE LTRIM (RTRIM (ITEM_CODE)) = MSI.SEGMENT1
AND MSI.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND MSI.ORGANIZATION_ID = 426 --342,343
AND MSI.ORGANIZATION_ID = MSIF.ORGANIZATION_ID
AND LTRIM(RTRIM(X.DIVISION_CODE))='777'
AND X.INVENTORY_ORG_CODE='426'
AND MSIF.LOCATOR_TYPE = 5
AND X.QTY > 0
AND X.ITEM_CODE !='15AMPS-PLUGS';
-- 46789 COGS
/* MAIN PROGRAM */
BEGIN
DBMS_OUTPUT.PUT_LINE ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
FOR R IN ITEM_C
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO L_TRANSACTION_INTERFACE_ID
FROM DUAL;
BEGIN
/* INSERTING INTO MAIN TRANSACTION TABLE */
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(SOURCE_CODE, SOURCE_LINE_ID,
SOURCE_HEADER_ID, COST_GROUP_ID, PROCESS_FLAG, TRANSACTION_MODE,
TRANSACTION_INTERFACE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
SUBINVENTORY_CODE, TRANSACTION_QUANTITY, TRANSACTION_UOM,
TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_TYPE_ID,
TRANSACTION_COST, TRANSACTION_REFERENCE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, LOCATOR_ID
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, R.COST_GROUP_ID, 1, 3,
L_TRANSACTION_INTERFACE_ID, R.INVENTORY_ITEM_ID, R.ORGANIZATION_ID,
R.SUBINVENTORY_CODE, TO_NUMBER (R.QUANTITY), R.PRIMARY_UOM_CODE,
'02-AUG-2011', R.MATERIAL_ACCOUNT, 40,
TO_NUMBER (R.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, R.LAST_EXP_DATE, R.LAST_ISSUE_DATE,
R.LAST_RECEIPT_DATE, R.LAST_SUPPLIER_NO, R.LOCATOR_ID
);
-- COMMIT;
/* UPDATES THE PROCESS FLAG TO 'Y' */
UPDATE UETDT.UET_INV_OPENING_BALANCES_INT
SET X_OB_IMPORTED = 'Y'
WHERE ITEM_CODE = R.ITEM_CODE AND QTY = R.QUANTITY AND UNIT_COST = R.COST
AND VALUE = R.VALUE;
-- COMMIT;
L_ITEM_COUNT := L_ITEM_COUNT + 1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Exception..');
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of normal items processed ' || L_ITEM_COUNT);
DBMS_OUTPUT.PUT_LINE ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;
COMMIT;
SELECT * FROM MTL_TRANSACTIONS_INTERFACE WHERE SOURCE_CODE = '1'
Go to inventory Setup > Transactions > Interface Managers > Run Material transaction ( Launch )
-- Upload data from user xls to temp table
-- run a cursor to plae data from temp table
REM +=======================================================================+
REM | |
REM | |
REM +=======================================================================+
REM | FILENAME |
REM | |
REM | |
REM | DESCRIPTION |
REM | This script has been created to Uplaod Inventory opening balances |
REM | from legacy system to Oracle 11i/R12 inventory |
REM | |
REM | 1. Update User XLS data into an TEMP table |
REM | 2. Run a cursor to move data from temp table to pre int table |
REM | 3. Run a plsql block to migrate data from |
REM | pre_interface_table to oracle std interface table |
REM | 4. Launch Transaction manager from Inventory |
REM +=======================================================================+
CREATE TABLE APPS.AEO_ITM_LOAD_TMP
(
PART_CODE VARCHAR2(240 BYTE),
DIVISION VARCHAR2(3 BYTE),
PARTS_NAME VARCHAR2(240 BYTE),
STOCK_RECD NUMBER,
BALANCE NUMBER,
ISSUE_DATE DATE,
UNIT_PRICE NUMBER,
BRAND VARCHAR2(240 BYTE),
PRODUCT VARCHAR2(240 BYTE),
MODEL_NO VARCHAR2(240 BYTE)
)
COMMIT;
-- Onhand 22 @ rate of 50 , and sysdate-50 is last issue date
INSERT INTO AEO_ITM_LOAD_TMP
VALUES ('RARDE00093',
777,
'RAR Switch - Ignition',
22,
22,
SYSDATE - 50,
50,
'Daewoo ',
NULL,
NULL)
SELECT * FROM APPS.AEO_ITM_LOAD_TMP
------------------- Upload XLS data in Above created Table ------------------------
-- Create a pre interface table
CREATE TABLE UET_INV_OPENING_BALANCES_INT
(
DIVISION_CODE VARCHAR2(30 BYTE),
INVENTORY_ORG_CODE VARCHAR2(9 BYTE),
SUBINVENTORY_NAME VARCHAR2(15 BYTE),
ITEM_CODE VARCHAR2(90 BYTE),
PRIMARY_UOM_CODE VARCHAR2(9 BYTE),
QTY NUMBER,
UNIT_COST NUMBER,
VALUE NUMBER,
STOCK_LOCATOR VARCHAR2(75 BYTE),
LOT_NUMBER VARCHAR2(90 BYTE),
EXPIRY_DATE DATE,
LAST_ISSUE_DATE DATE,
LAST_RECEIPT_DATE DATE,
LAST_SUPPLIER_NO VARCHAR2(120 BYTE),
X_OB_IMPORTED CHAR(1 BYTE)
)
DELETE FROM UETDT.UET_INV_OPENING_BALANCES_INT
COMMIT;
-- Move data from TMP table to pre interface table with validations
DECLARE
N NUMBER;
CURSOR C1
IS
SELECT * FROM AEO_ITM_LOAD_TMP;
BEGIN
FOR REC IN C1
LOOP
INSERT INTO UETDT.UET_INV_OPENING_BALANCES_INT (DIVISION_CODE,
INVENTORY_ORG_CODE,
SUBINVENTORY_NAME,
ITEM_CODE,
PRIMARY_UOM_CODE,
QTY,
UNIT_COST,
VALUE,
STOCK_LOCATOR,
LOT_NUMBER,
EXPIRY_DATE,
LAST_ISSUE_DATE,
LAST_RECEIPT_DATE,
LAST_SUPPLIER_NO,
X_OB_IMPORTED)
VALUES (REC.DIVISION,
426, ----- INVENTORY_ORG_CODE
'ELECTRONIC',
REC.PART_CODE,
'PCE',
REC.BALANCE,
REC.UNIT_PRICE,
REC.BALANCE * REC.UNIT_PRICE,
'SPARES',
NULL,
NULL,
REC.ISSUE_DATE,
REC.ISSUE_DATE,
REC.BRAND,
'N');
COMMIT;
dbms_output.put_line('Started populating Inventory Opening Balances at TEMP Table : '|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
END LOOP;
END;
SELECT * FROM UETDT.UET_INV_OPENING_BALANCES_INT
------------------------ Populating Inventory Opening Balances -------------------------------
-- Material acount is Receipt account not COGS
DECLARE
L_TRANSACTION_INTERFACE_ID NUMBER;
L_ITEM_COUNT NUMBER := 0;
L_EXP_COUNT NUMBER := 0;
CURSOR ITEM_C
IS
SELECT X.ITEM_CODE OLD_ITEM_CODE, X.QTY QUANTITY, X.UNIT_COST COST, X.LAST_ISSUE_DATE,
X.LAST_RECEIPT_DATE, X.LAST_SUPPLIER_NO, X.ITEM_CODE, X.EXPIRY_DATE LAST_EXP_DATE,
X.VALUE, MSI.ORGANIZATION_ID, MSI.RESTRICT_LOCATORS_CODE,
MSIF.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
GET_LOCATOR_ID(MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, X.STOCK_LOCATOR) LOCATOR_ID,
53322 MATERIAL_ACCOUNT, MSI.PRIMARY_UOM_CODE, MSI.INVENTORY_ITEM_ID,
P.DEFAULT_COST_GROUP_ID COST_GROUP_ID
FROM UETDT.UET_INV_OPENING_BALANCES_INT X,
MTL_SYSTEM_ITEMS MSI,
MTL_SECONDARY_INVENTORIES_FK_V MSIF,
MTL_PARAMETERS P
WHERE LTRIM (RTRIM (ITEM_CODE)) = MSI.SEGMENT1
AND MSI.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND MSI.ORGANIZATION_ID = 426 --342,343
AND MSI.ORGANIZATION_ID = MSIF.ORGANIZATION_ID
AND LTRIM(RTRIM(X.DIVISION_CODE))='777'
AND X.INVENTORY_ORG_CODE='426'
AND MSIF.LOCATOR_TYPE = 5
AND X.QTY > 0
AND X.ITEM_CODE !='15AMPS-PLUGS';
-- 46789 COGS
/* MAIN PROGRAM */
BEGIN
DBMS_OUTPUT.PUT_LINE ( 'Started populating Inventory Opening Balances at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
FOR R IN ITEM_C
LOOP
SELECT XUET_TRAN_INTERFACE_ID_S.NEXTVAL
INTO L_TRANSACTION_INTERFACE_ID
FROM DUAL;
BEGIN
/* INSERTING INTO MAIN TRANSACTION TABLE */
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(SOURCE_CODE, SOURCE_LINE_ID,
SOURCE_HEADER_ID, COST_GROUP_ID, PROCESS_FLAG, TRANSACTION_MODE,
TRANSACTION_INTERFACE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
SUBINVENTORY_CODE, TRANSACTION_QUANTITY, TRANSACTION_UOM,
TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_TYPE_ID,
TRANSACTION_COST, TRANSACTION_REFERENCE, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN, ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15, LOCATOR_ID
)
VALUES (1, XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL,
XUET_TRAN_SOURCE_LINE_IDS_S.NEXTVAL, R.COST_GROUP_ID, 1, 3,
L_TRANSACTION_INTERFACE_ID, R.INVENTORY_ITEM_ID, R.ORGANIZATION_ID,
R.SUBINVENTORY_CODE, TO_NUMBER (R.QUANTITY), R.PRIMARY_UOM_CODE,
'02-AUG-2011', R.MATERIAL_ACCOUNT, 40,
TO_NUMBER (R.COST), 'OPENING BALANCE', SYSDATE, -1,
-1, SYSDATE, -1, R.LAST_EXP_DATE, R.LAST_ISSUE_DATE,
R.LAST_RECEIPT_DATE, R.LAST_SUPPLIER_NO, R.LOCATOR_ID
);
-- COMMIT;
/* UPDATES THE PROCESS FLAG TO 'Y' */
UPDATE UETDT.UET_INV_OPENING_BALANCES_INT
SET X_OB_IMPORTED = 'Y'
WHERE ITEM_CODE = R.ITEM_CODE AND QTY = R.QUANTITY AND UNIT_COST = R.COST
AND VALUE = R.VALUE;
-- COMMIT;
L_ITEM_COUNT := L_ITEM_COUNT + 1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Exception..');
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of normal items processed ' || L_ITEM_COUNT);
DBMS_OUTPUT.PUT_LINE ( 'Populating Inventory Opening Balances Completed at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS')
);
END;
COMMIT;
SELECT * FROM MTL_TRANSACTIONS_INTERFACE WHERE SOURCE_CODE = '1'
Go to inventory Setup > Transactions > Interface Managers > Run Material transaction ( Launch )
Item Import and Sub Inventory Assignment
Oracle Item Import and Sub Inventory Assignment
Create a Temp table ( Attribute1 represents Supplier Id )
Column Name Null? Data Type
SEGMENT1 Y VARCHAR2 (40 Byte)
SEGMENT2 Y VARCHAR2 (40 Byte)
DESCRIPTION Y VARCHAR2 (240 Byte)
ORGANIZATION_ID N NUMBER
PRIMARY_UOM_CODE Y VARCHAR2 (3 Byte)
ATTRIBUTE1 Y VARCHAR2 (240 Byte)
/* Insert Test Data into Table */
insert into aeo_item_import values ('RARDE00093','551','RAR Switch - Ignition',423,'PCE',214658) ;
commit ;
/* Insert into interface Table
Keep a eye on Item Template_id its predefined template id */
INSERT INTO apps.mtl_system_items_interface
( process_flag,transaction_type,set_process_id,segment1, segment2,
attribute1,organization_id, description,primary_uom_code ,template_id,created_by,
creation_date, last_update_date, last_updated_by
)
(select '1' ,'CREATE','0', segment1,segment2, attribute1,organization_id,
description,primary_uom_code,1, -1, SYSDATE, SYSDATE, -1
from aeo_item_import );
COMMIT;
Go to Oracle Inventory Items > Import > Import Item
Check Item is Created and assigned to 423 (organization_id which is Master Org )
Now we want to assign item to Child Org .
repeat above all steps just change organization_id from 423 to child organizations_id
--------------------------------------------------------------
/* Assigning Sub inventories to Items Created just now */
DECLARE
l_item_id NUMBER;
l_item_count NUMBER;
l_vendor_id NUMBER := 0;
l_err_flag CHAR (1) := 'N';
l_organization_id NUMBER := 0;
m_exists varchar2 (1);
/* Selecting Sub Inventories from custom table for populated items */
-- Select the items that have been enabled through the mass enable script ----
CURSOR c
IS
SELECT SEGMENT1,
SEGMENT2,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
FROM apps.MTL_SYSTEM_ITEMS
WHERE segment1 IN (SELECT segment1 FROM aeo_item_import)
AND--AND trunc(creation_date) = trunc(sysdate)
ORGANIZATION_ID = 423;
-- 423 is organization_id for which you want to Insert sub inventories
CURSOR sub_invs (p_organization_id IN NUMBER)
IS
SELECT DISTINCT secondary_inventory_name
FROM apps.mtl_secondary_inventories
WHERE organization_id = 425; -- Master Org
/* ---- Main Program ---- */
BEGIN
DBMS_OUTPUT.put_line('======================================================================');
DBMS_OUTPUT.put_line('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
l_item_count := 0;
FOR r IN c
LOOP
l_item_id := NULL;
l_vendor_id := NULL;
l_err_flag := 'N';
l_organization_id := NULL;
IF (l_err_flag <> 'Y')
THEN
FOR r1 IN sub_invs (r.organization_id)
LOOP
BEGIN
BEGIN
-- Check if the item- organization - subinventory combination is already existing --
SELECT 'm'
INTO m_exists
FROM apps.mtl_item_sub_inventories
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id
AND secondary_inventory =
r1.secondary_inventory_name;
--dbms_output.put_line('Inventory_item_ID :'||r.inventory_item_id);
--dbms_output.put_line('Organization_ID :'||r.organization_id);
--dbms_output.put_line('Sub Inventory :'||r1.secondary_inventory_name);
----- If the combination doesnot exist then populate to the subinventory table ----------------
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- DBMS_OUTPUT.PUT_LINE(r.inventory_item_id || '-' || r.organization_id||'-'||r1.secondary_inventory_name);
INSERT INTO apps.mtl_item_sub_inventories (
inventory_item_id,
organization_id,
secondary_inventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_planning_code
)
VALUES (r.inventory_item_id,
r.ORGANIZATION_ID,
r1.secondary_inventory_name,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);
-- COMMIT; -- Committing the Insert --
l_item_count := l_item_count + 1;
/* Update the Flag */
UPDATE apps.mtl_system_items
SET attribute15 = 'Y'
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id;
-- COMMIT; -- Comitting the Update --
END; -- End of Check procedure ---
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line (
'Total Records populated into Item Sub-Inventories : ' || l_item_count
);
DBMS_OUTPUT.put_line('Completed populating Item Sub-Inventories at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.put_line('======================================================================');
END;
Create a Temp table ( Attribute1 represents Supplier Id )
Column Name Null? Data Type
SEGMENT1 Y VARCHAR2 (40 Byte)
SEGMENT2 Y VARCHAR2 (40 Byte)
DESCRIPTION Y VARCHAR2 (240 Byte)
ORGANIZATION_ID N NUMBER
PRIMARY_UOM_CODE Y VARCHAR2 (3 Byte)
ATTRIBUTE1 Y VARCHAR2 (240 Byte)
/* Insert Test Data into Table */
insert into aeo_item_import values ('RARDE00093','551','RAR Switch - Ignition',423,'PCE',214658) ;
commit ;
/* Insert into interface Table
Keep a eye on Item Template_id its predefined template id */
INSERT INTO apps.mtl_system_items_interface
( process_flag,transaction_type,set_process_id,segment1, segment2,
attribute1,organization_id, description,primary_uom_code ,template_id,created_by,
creation_date, last_update_date, last_updated_by
)
(select '1' ,'CREATE','0', segment1,segment2, attribute1,organization_id,
description,primary_uom_code,1, -1, SYSDATE, SYSDATE, -1
from aeo_item_import );
COMMIT;
Go to Oracle Inventory Items > Import > Import Item
Check Item is Created and assigned to 423 (organization_id which is Master Org )
Now we want to assign item to Child Org .
repeat above all steps just change organization_id from 423 to child organizations_id
--------------------------------------------------------------
/* Assigning Sub inventories to Items Created just now */
DECLARE
l_item_id NUMBER;
l_item_count NUMBER;
l_vendor_id NUMBER := 0;
l_err_flag CHAR (1) := 'N';
l_organization_id NUMBER := 0;
m_exists varchar2 (1);
/* Selecting Sub Inventories from custom table for populated items */
-- Select the items that have been enabled through the mass enable script ----
CURSOR c
IS
SELECT SEGMENT1,
SEGMENT2,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
FROM apps.MTL_SYSTEM_ITEMS
WHERE segment1 IN (SELECT segment1 FROM aeo_item_import)
AND--AND trunc(creation_date) = trunc(sysdate)
ORGANIZATION_ID = 423;
-- 423 is organization_id for which you want to Insert sub inventories
CURSOR sub_invs (p_organization_id IN NUMBER)
IS
SELECT DISTINCT secondary_inventory_name
FROM apps.mtl_secondary_inventories
WHERE organization_id = 425; -- Master Org
/* ---- Main Program ---- */
BEGIN
DBMS_OUTPUT.put_line('======================================================================');
DBMS_OUTPUT.put_line('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
l_item_count := 0;
FOR r IN c
LOOP
l_item_id := NULL;
l_vendor_id := NULL;
l_err_flag := 'N';
l_organization_id := NULL;
IF (l_err_flag <> 'Y')
THEN
FOR r1 IN sub_invs (r.organization_id)
LOOP
BEGIN
BEGIN
-- Check if the item- organization - subinventory combination is already existing --
SELECT 'm'
INTO m_exists
FROM apps.mtl_item_sub_inventories
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id
AND secondary_inventory =
r1.secondary_inventory_name;
--dbms_output.put_line('Inventory_item_ID :'||r.inventory_item_id);
--dbms_output.put_line('Organization_ID :'||r.organization_id);
--dbms_output.put_line('Sub Inventory :'||r1.secondary_inventory_name);
----- If the combination doesnot exist then populate to the subinventory table ----------------
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- DBMS_OUTPUT.PUT_LINE(r.inventory_item_id || '-' || r.organization_id||'-'||r1.secondary_inventory_name);
INSERT INTO apps.mtl_item_sub_inventories (
inventory_item_id,
organization_id,
secondary_inventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_planning_code
)
VALUES (r.inventory_item_id,
r.ORGANIZATION_ID,
r1.secondary_inventory_name,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);
-- COMMIT; -- Committing the Insert --
l_item_count := l_item_count + 1;
/* Update the Flag */
UPDATE apps.mtl_system_items
SET attribute15 = 'Y'
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id;
-- COMMIT; -- Comitting the Update --
END; -- End of Check procedure ---
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.put_line (
'Total Records populated into Item Sub-Inventories : ' || l_item_count
);
DBMS_OUTPUT.put_line('Completed populating Item Sub-Inventories at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.put_line('======================================================================');
END;
Subscribe to:
Posts (Atom)