Oracle EBS (ERP): Script

Showing posts with label Script. Show all posts

Oracle EBS Script for resetting password

--oracle apps reset password from backend

DECLARE

p_change BOOLEAN;

v_user VARCHAR2(400) := 'Support';

v_new_password VARCHAR2(400) := 'inJG@2019';

BEGIN

p_change := fnd_user_pkg.changepassword(username => v_user, newpassword => v_new_password);

IF p_change THEN

dbms_output.put_line('Password has been successfully reset for user ' || v_user);

ELSE

dbms_output.put_line('Password reset has been failed for user ' || v_user);

END IF;

COMMIT;

END;

Oracle EBS Active User and Person name list Query

SELECT fu.user_name,

fu.user_id,

fu.EMAIL_ADDRESS,

(select hr.FULL_NAME from PER_PEOPLE_F hr

where fu.employee_id = hr.person_id

and nvl(hr.effective_start_date, sysdate-1) < sysdate

and nvl(hr.effective_END_date, sysdate+1) > sysdate) PERSON_NAME,

fu.FAX,

fu.start_date,

fu.end_date,

fu.description,

ftl.application_name,

frv.responsibility_name,

a.start_date responsibility_start_date,

a.end_date responsibility_end_date

FROM

fnd_user fu,

fnd_responsibility_vl frv,

fnd_user_resp_groups_direct a,

fnd_application fa,

fnd_application_tl ftl

WHERE a.user_id = fu.user_id

AND a.responsibility_id = frv.responsibility_id

AND fa.application_id = a.responsibility_application_id

AND fa.application_id = ftl.application_id

AND ftl.language = USERENV('LANG')

AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)

AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE + 1)

AND SYSDATE BETWEEN frv.start_date AND NVL(frv.end_date,SYSDATE + 1)

AND fu.user_id > 1100 -- Excludes standard users like SYSADMIN,GUEST etc

UNION ALL

SELECT DISTINCT fu.user_name,

fu.user_id,

fu.EMAIL_ADDRESS,

(select hr.FULL_NAME from PER_PEOPLE_F hr

where fu.employee_id = hr.person_id

and nvl(hr.effective_start_date, sysdate-1) < sysdate

and nvl(hr.effective_END_date, sysdate+1) > sysdate) PERSON_NAME,

fu.FAX,

fu.start_date,

fu.end_date,

fu.description,

ftl.application_name,

frv.responsibility_name,

a.start_date responsibility_start_date,

a.end_date responsibility_end_date

FROM

fnd_user fu,

fnd_responsibility_vl frv,

fnd_user_resp_groups_indirect a,

fnd_application fa,

fnd_application_tl ftl

WHERE a.user_id = fu.user_id

AND a.responsibility_id = frv.responsibility_id

AND fa.application_id = a.responsibility_application_id

AND fa.application_id = ftl.application_id

AND ftl.language = USERENV('LANG')

AND SYSDATE BETWEEN a.start_date AND NVL(a.end_date,SYSDATE + 1)

AND SYSDATE BETWEEN fu.start_date AND NVL(fu.end_date,SYSDATE + 1)

AND SYSDATE BETWEEN frv.start_date AND NVL(frv.end_date,SYSDATE + 1)

AND fu.user_id > 1100 -- Excludes standard users like SYSADMIN,GUEST etc

ORDER BY user_name

Oracle EBS Audit Query

Query 1 – List of Oracle Forms Last Modified during the period under review

SELECT a.form_name "Form Name",

b.user_form_name "User From Name",b.description,

a.creation_date "Creation Date",

a.last_update_date "Last Update Date",

a.LAST_UPDATED_BY

FROM fnd_form a, fnd_form_tl b

WHERE a.form_id = b.form_id

AND TO_DATE (a.last_update_date) >= TO_DATE ('01-Apr-2023')

ORDER BY TRUNC (a.last_update_date);

Query 2 – List of Oracle Concurrent Programs Last Modified during the period under review

select a.concurrent_program_name,b.user_concurrent_program_name,b.description, a.execution_method_code,

a.creation_date,a.last_update_date, a.LAST_UPDATED_BY

from fnd_concurrent_programs a, fnd_concurrent_programs_tl b

where a.concurrent_program_id = b.concurrent_program_id

and to_date(a.last_update_date)>= ('01-Apr-2023')

order by trunc(a.last_update_date);

Query 3 - List of all profile option changes :

SELECT pro.profile_option_id,

SUBSTR(pro.profile_option_name,1,35) Profile,

pov.level_id,pov.level_value,

DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,

DECODE(pov.level_id, 10001,'Site') Level_Value,

NVL(pov.profile_option_value,'Is Null') Profile_option_Value,

to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,

(select user_name from FND_USER where user_id=pov.created_by) created_by,

(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,

to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date

From FND_PROFILE_OPTION_VALUES pov,

FND_PROFILE_OPTIONS pro

Where 1=1

AND pro.profile_option_id = pov.profile_option_id

AND pov.level_id=10001

UNION

SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,

DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,

DECODE(pov.level_id,10002,appl.application_short_name) Level_Value,

NVL(pov.profile_option_value,'Is Null') Profile_option_Value,

to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,

(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date

FROM FND_PROFILE_OPTION_VALUES pov,

FND_PROFILE_OPTIONS pro,

FND_APPLICATION appl

Where 1=1

AND pro.profile_option_id = pov.profile_option_id

AND pov.level_id=10002

AND pov.level_value = appl.application_id

UNION

SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,

DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,

DECODE(pov.level_id, 10003,resp.responsibility_name) Level_Value,

NVL(pov.profile_option_value,'Is Null') Profile_option_Value, to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,

(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date

FROM FND_PROFILE_OPTION_VALUES pov,

FND_RESPONSIBILITY_TL resp,

FND_PROFILE_OPTIONS pro

WHERE 1=1

AND pro.profile_option_id = pov.profile_option_id

AND pov.level_value = resp.responsibility_id

AND pov.level_id=10003

UNION

SELECT pro.profile_option_id,SUBSTR(pro.profile_option_name,1,35) Profile,pov.level_id,pov.level_value,

DECODE(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level,

DECODE(pov.level_id, 10004,u.user_name) Level_Value,

NVL(pov.profile_option_value,'Is Null') Profile_option_Value,to_char(pov.creation_date,'dd-mon-yyyy hh24:mi:ss') creation_date,(select user_name from FND_USER where user_id=pov.created_by) created_by,

(select user_name from FND_USER where user_id=pov.last_updated_by) last_updated_by,to_char(pov.last_update_date,'dd-mon-yyyy hh24:mi:ss') last_update_date

FROM FND_PROFILE_OPTION_VALUES pov,

FND_PROFILE_OPTIONS pro,

FND_USER u

WHERE 1=1

AND pro.profile_option_id = pov.profile_option_id

AND pov.level_value = u.user_id

AND pov.level_id=10004

Order BY 1,2;

Query 4 - System generated list of all patch changes made to the Oracle Oracle ERP during the audit period.

SELECT (SELECT name FROM v$database) SID,

AP.PATCH_NAME,

AP.PATCH_TYPE,

AD.DRIVER_FILE_NAME,

TO_CHAR (AD.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE,

AP.LAST_UPDATE_DATE,

AP.LAST_UPDATED_BY,

AD.PLATFORM,

AL.LANGUAGE

FROM AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL

WHERE AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID

AND TO_DATE (AP.LAST_UPDATE_DATE) >= TO_DATE ('01-Apr-2023')

AND TO_DATE (AP.LAST_UPDATE_DATE) <= TO_DATE ('31-Mar-2024')

AND AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID

ORDER BY AP.LAST_UPDATE_DATE DESC;

Parameter wise Concurrent Program Find

SELECT v.request_id, v.program,

v.requestor,

(Select FULL_NAME from per_all_people_f where EMPLOYEE_NUMBER = v.requestor) requestor_name,

(Select APPLICATION_NAME from fnd_application_tl where APPLICATION_ID= v.program_application_id)PROg_APPL,

v.argument_text,

v.actual_completion_date,

v.completion_text,

v.requested_by,

(Select APPLICATION_NAME from fnd_application_tl where APPLICATION_ID= v.responsibility_application_id)Respo_APPL,

(Select RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_ID=v.responsibility_id)RESPONSIBILITY_NAME,

v.controlling_manager,

v.program_short_name,

v.priority_request_id,

v.requested_start_date,

v.concurrent_program_id,

v.enabled,

v.phase_code,

v.hold_flag,

v.status_code

FROM

fnd_conc_req_summary_v v

WHERE

argument_text LIKE :Parameter

AND ( request_date >= trunc(sysdate - 90) )

ORDER BY

request_id DESC

Oracle R12 Form personalization List Script

Query to list form personalization in oracle apps

SELECT DISTINCT ffc.sequence,

ffc.form_name,

ffc.function_name,

ffc.description personalization_rule_name,

ffc.enabled,

ff.user_form_name,

fat.application_name,

ffc.trigger_event,

ffc.condition,

ffc.creation_date

FROM

fnd_form_custom_rules ffc,

fnd_form b,

fnd_form_tl ff,

fnd_application_tl fat

WHERE

ffc.enabled = 'Y'

AND ffc.form_name = b.form_name

AND b.form_id = ff.form_id

AND b.application_id = fat.application_id

AND ffc.creation_date > '01-04-22'

ORDER BY ffc.form_name,

fat.application_name;

Oracle R12 AP Payment status Transmitte script

-- Find the payment_instruction_id form below script

SELECT

payment_instruction_id PaymentInstructionID,

payment_instruction_status,

payments_complete_code

FROM

iby_pay_instructions_all

WHERE

pay_admin_assigned_ref_code = &PPR_Number;

----Payment Transmitted script below

SET SERVEROUTPUT ON SIZE 1000000;

SET LINESIZE 2000;

DECLARE

l_return_status VARCHAR2(1000);

l_instr_id NUMBER;

l_instr_status VARCHAR2(240);

l_completion_code VARCHAR2(240);

l_msg_index_out NUMBER;

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

l_instr_id := &PaymentInstructionID;

DBMS_OUTPUT.PUT_LINE('Payment instruction ID : '|| l_instr_id);

SELECT payment_instruction_status,

payments_complete_code

INTO l_instr_status,

l_completion_code

FROM iby_pay_instructions_all

WHERE payment_instruction_id = l_instr_id;

IF (NVL(l_completion_code,'NO') <> 'YES') THEN

DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);

DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);

DBMS_OUTPUT.PUT_LINE('Attempting to unlock pmt instruction id: '|| l_instr_id);

IBY_DISBURSE_UI_API_PUB_PKG.unlock_pmt_entity( l_instr_id, 'PAYMENT_INSTRUCTION', l_return_status );

DBMS_OUTPUT.PUT_LINE('Finished unlocking pmt instruction id: '|| l_instr_id);

DBMS_OUTPUT.PUT_LINE('Attempting to terminate pmt instruction id: '|| l_instr_id);

IBY_DISBURSE_UI_API_PUB_PKG.terminate_pmt_instruction( l_instr_id, l_instr_status, l_return_status );

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

DBMS_OUTPUT.PUT_LINE('ERROR: API return status: ' || l_return_status );

DBMS_OUTPUT.PUT_LINE('ERROR: API call failed');

DBMS_OUTPUT.PUT_LINE('Rolling back changes ...');

ROLLBACK;

ELSE

DBMS_OUTPUT.PUT_LINE('INFO: API return status: ' || l_return_status );

DBMS_OUTPUT.PUT_LINE('INFO: API call success');

DBMS_OUTPUT.PUT_LINE('Committing ...');

COMMIT;

END IF;

ELSE

DBMS_OUTPUT.PUT_LINE('Payment instruction status : '|| l_instr_status);

DBMS_OUTPUT.PUT_LINE('Payment instruction completion code : '|| l_completion_code);

DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction cannot be terminated since it is already complete');

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('ERROR: Payment instruction not found !');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: Exception occured when executing termination script !');

DBMS_OUTPUT.PUT_LINE('SQLCODE is: ' || SQLCODE);

DBMS_OUTPUT.PUT_LINE('SQLERRM is: ' || SQLERRM);

END;

EXIT;

Oracle R12: AP: Expense Report Export Process or Rejects Query

--Expense Reports on Oracle iProcurement

select * from ap_expense_report_lines_all

where amount is null

and report_header_id in (

select report_header_id

from ap_expense_report_headers_all

where invoice_num = :p_expense_report_num);

select * from ap_exp_report_dists_all

where amount is null

and report_header_id in (

select report_header_id

from ap_expense_report_headers_all

where invoice_num = :p_expense_report_num);

Oracle R12 employee update script hr_assignment_api.update_emp_asg in HRMS

--Employee Expense Account update and Assignment

CREATE TABLE xx_emp_code_comb_tbl

(

emp_code VARCHAR2(30 BYTE),

emp_full_name VARCHAR2(240 BYTE),

creation_date DATE,

ledger VARCHAR2(240 BYTE),

code_combination VARCHAR2(240 BYTE),

status VARCHAR2(1 BYTE),

code_comb_id NUMBER

)

---Below is the script for update the assignment

DECLARE

i_effective_date DATE := SYSDATE;

i_assignment_id NUMBER ;--:= 407; -- Pass assignment_id created by hr_employee_api.create_employee

i_assignment_number VARCHAR2(30) :='JG123'; -- Pass parameter assignment_number created by hr_employee_api.create_employee

l_obj NUMBER;

l_assignment_number VARCHAR2(30);

l_supervisor_id NUMBER;

lo_soft_coding_keyflex_id NUMBER;

o_concatenated_segments VARCHAR2(240);

o_comment_id NUMBER;

o_effective_start_date DATE;

o_effective_end_date DATE;

o_no_managers_warning BOOLEAN;

o_other_manager_warning BOOLEAN;

Cursor C1

SELECT paaf.assignment_number,paaf.object_version_number, assignment_id,a.code_comb_id,paaf.effective_start_date

FROM per_all_assignments_f paaf,

xx_emp_code_comb_tbl a,

per_assignment_status_types past

WHERE 1=1 --assignment_id = i_assignment_id

AND paaf.assignment_number = a.emp_code

AND TRUNC(SYSDATE) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)

AND UPPER(past.user_status) = 'ACTIVE ASSIGNMENT'

AND past.assignment_status_type_id = paaf.assignment_status_type_id;

BEGIN

FOR i IN C1 LOOP

hr_assignment_api.update_emp_asg

(p_effective_date => i.effective_start_date

,p_datetrack_update_mode => 'CORRECTION'

,p_assignment_id => i.assignment_id

,p_assignment_number => i.assignment_number

,p_object_version_number => i.object_version_number

--,p_supervisor_id => 65 --l_supervisor_id

,p_default_code_comb_id => i.code_comb_id

,p_set_of_books_id => 2041

-- OUT Variables

,p_soft_coding_keyflex_id => lo_soft_coding_keyflex_id

,p_concatenated_segments => o_concatenated_segments

,p_comment_id => o_comment_id

,p_effective_start_date => o_effective_start_date

,p_effective_end_date => o_effective_end_date

,p_no_managers_warning => o_no_managers_warning

,p_other_manager_warning => o_other_manager_warning

);

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Employee Supervisor is updated');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error while updating Employee Supervisor : '||SQLERRM);

END;

API to create Code Combination and get CCID

----API To Create Bulk GL Code Combinations

DECLARE

l_ledger_name VARCHAR2(300)DEFAULT 'JGTECH_INDIA_PRIMARY_LEDGER';

l_structure_number NUMBER;

l_sucess BOOLEAN;

CURSOR CUR

SELECT a.rowid rid,

a.emp_code,

a.code_combination

FROM xx_emp_code_comb a

WHERE code_comb_id is null;

BEGIN

SELECT chart_of_accounts_id

INTO l_structure_number

FROM gl_ledgers

WHERE name = l_ledger_name;

FOR i IN cur LOOP

lb_sucess := fnd_flex_keyval.validate_segs

(

operation => 'CREATE_COMBINATION',

appl_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => l_structure_number,

concat_segments => i.code_combination,

validation_date => SYSDATE

);

IF l_sucess

THEN

DBMS_OUTPUT.PUT_LINE('Successful. Code Combination ID: '||fnd_flex_keyval.combination_id());

UPDATE xx_emp_code_comb aa

SET code_comb_id=fnd_flex_keyval.combination_id()

WHERE aa.emp_code=i.emp_code;

ELSE

DBMS_OUTPUT.PUT_LINE('Error creating a Code combination id for ' ||'/i.code_combination/'||' Error:'|| fnd_flex_keyval.error_message());

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unable to create a code combination for ' ||'/'||' Error:'|| SQLERRM);

END;

How to Get Application User Login Session Details

---Get Application User Login Session Details Oracle R12

SELECT

SUBSTR(d.user_name,1,50) User_Name

, a.pid Process_ID

, b.sid Session_ID

, b.serial#

, to_char(c.start_time,'DD-MON-RRRR HH:MI:SS AM') start_time

, to_char(c.end_time,'DD-MON-RRRR HH:MI:SS AM') end_time

FROM

v$process a

, v$session b

, fnd_logins c

, fnd_user d

WHERE a.pid = c.pid

AND d.user_name = UPPER (:p_user_name)

AND d.user_id = c.user_id

AND a.addr = b.paddr ;

How many concurrent program Running R12

Select fcr.request_id,fcp .*

FROM apps.fnd_concurrent_requests fcr ,

apps.fnd_user fu ,

apps.fnd_concurrent_programs fcp ,

apps.fnd_concurrent_programs_tl fcpt ,

apps.fnd_printer_styles_tl fpst ,

apps.fnd_conc_release_classes fcrc ,

apps.fnd_responsibility_tl frt ,

apps.fnd_lookups fl

WHERE fcp.application_id = fcpt.application_id

AND fcr.requested_by = fu.user_id

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id

AND fcr.concurrent_program_id = fcpt.concurrent_program_id

AND fcr.responsibility_id = frt.responsibility_id

AND fcr.print_style = fpst.printer_style_name(+)

AND fcr.release_class_id = fcrc.release_class_id(+)

AND fcr.status_code = fl.lookup_code

AND fl.lookup_type = 'CP_STATUS_CODE'

AND fcr.phase_code = 'P'

AND frt.language = 'US'

AND fpst.language = 'US'

AND fcpt.language = 'US'

Oracle PL/SQL to XML Report Generate

---Package/Procedure to generate XML report

CREATE OR REPLACE PACKAGE apps.XXJG_PRD_WISE_RPT_PKG

-- +====================================================================+

-- | BexPharmaceuticals Limited |

-- +====================================================================+

-- Owner : BexPharmaceuticals Limited

-- Module(s) : OPM

-- File name : XXJG_PRD_WISE_RPT_PKG

-- Doc Ref(s) : FS_OPM-R-002

-- Description: Product Wise Number of Batches Report

-- Modification History

-- --------------------------

--- Version Date Author Change Reference / Description

-- ------- ----------- ------------------- ------------------------------------

--1.0 05-FEB-2020 Jayanta Ganguly Initial Creation

-- *************************************************************************

PROCEDURE XXJG_PRD_WISE_RPT_PRC (

x_errbuf OUT VARCHAR2,

x_retcode OUT NUMBER,

p_inv_org IN NUMBER,

p_department IN VARCHAR2,

p_resource IN VARCHAR2, ---Added by JG

p_from_date IN VARCHAR2,

p_to_date IN VARCHAR2,

-- p_dosage IN VARCHAR2, --Comment by JG

p_product_type IN VARCHAR2,

p_item_code IN VARCHAR2,

p_user_id IN VARCHAR2

);

END XXJG_PRD_WISE_RPT_PKG;

-------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.XXJG_PRD_WISE_RPT_PKG

-- +====================================================================+

-- | BexPharmaceuticals Limited |

-- +====================================================================+

-- Owner : BexPharmaceuticals Limited

-- Module(s) : OPM

-- File name : XXJG_PRD_WISE_RPT_PKG

-- Doc Ref(s) : FS_OPM-R-002

-- Description: Product Wise Number of Batches Report

-- Modification History

-- --------------------------

--- Version Date Author Change Reference / Description

-- ------- ----------- ------------------- ------------------------------------

--1.0 05-FEB-2020 Jayanta Ganguly Initial Creation

-- *************************************************************************/

PROCEDURE XXJG_PRD_WISE_RPT_PRC (

x_errbuf OUT VARCHAR2,

x_retcode OUT NUMBER,

p_inv_org IN NUMBER,

p_department IN VARCHAR2,

p_resource IN VARCHAR2,

p_from_date IN VARCHAR2,

p_to_date IN VARCHAR2,

-- p_dosage IN VARCHAR2, --Comment by JG

p_product_type IN VARCHAR2,

p_item_code IN VARCHAR2,

p_user_id IN VARCHAR2

)

CURSOR cur_main

SELECT abc.product_code,

REPLACE (abc.product_name, '&', ' and ') product_name,

abc.dosage_type, abc.item_type,

SUM (abc.plan_qty) batch_size, abc.product_type, uom,

MAX

(CASE abc.act_month

WHEN TO_CHAR (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month1,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month2,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month3,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month4,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month5,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month6,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month7,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month8,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month9,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month10,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month11,

MAX

(CASE abc.act_month

WHEN TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

THEN cnt

END

) AS month12,

SUM (cnt) tt_no_of_batch

FROM (SELECT msib.segment1 product_code,

msib.description product_name,

msib.attribute2 dosage_type,

(SELECT segment2

FROM mtl_item_categories_v mic

WHERE mic.category_set_name =

'Inventory'

AND mic.inventory_item_id =

msib.inventory_item_id

AND mic.organization_id = msib.organization_id)

item_type,

/*TO_CHAR (nvl(trunc(gbh.ACTUAL_START_DATE),trunc(gbh.plan_start_date)), 'MON-YY')*/

/* Change on 9-Mar-2020 For FG Batch

TO_CHAR (DECODE(p_department,'LNP',NVL(TRUNC(gbh.actual_start_date),TRUNC(gbh.plan_start_date))

,'SPL1',NVL(TRUNC(gbh.actual_start_date),TRUNC(gbh.plan_start_date))

,'SPL2',NVL(TRUNC(gbh.actual_start_date),TRUNC(gbh.plan_start_date))

,NVL(TRUNC(gbh.actual_start_date),TRUNC(TO_DATE(gbh.attribute1,'YYYY/MM/DD HH24:MI:SS'))) ) ,'MON-YY') act_month,*/

TO_CHAR

(DECODE

(p_department,

'LNP', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL1', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL2', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

TRUNC (TO_DATE (gbh.attribute1,

'YYYY/MM/DD HH24:MI:SS'

)

'MON-YY'

) act_month,

COUNT (*) cnt,

(SELECT std_qty

FROM gmd_recipe_validity_rules grv

WHERE 1 = 1

AND grv.organization_id = msib.organization_id

AND grv.preference = 1

AND ROWNUM = 1

AND grv.recipe_id IN (

SELECT gr2.recipe_id

FROM gmd_recipes gr2

WHERE gr2.recipe_no = msib.segment1

AND gr2.owner_organization_id = msib.organization_id

-- and gr2.RECIPE_VERSION =( select max(gr1.RECIPE_VERSION) from gmd_recipes gr1

-- where gr1.RECIPE_NO=msib.segment1

-- and gr1.OWNER_ORGANIZATION_ID= msib.organization_id)

)) plan_qty,

(SELECT detail_uom

FROM gmd_recipe_validity_rules grv

WHERE 1 = 1

AND grv.organization_id = msib.organization_id

AND grv.preference = 1

AND ROWNUM = 1

AND grv.recipe_id IN (

SELECT gr2.recipe_id

FROM gmd_recipes gr2

WHERE gr2.recipe_no = msib.segment1

AND gr2.owner_organization_id = msib.organization_id

-- and gr2.RECIPE_VERSION =( select max(gr1.RECIPE_VERSION) from gmd_recipes gr1

-- where gr1.RECIPE_NO=msib.segment1

-- and gr1.OWNER_ORGANIZATION_ID= msib.organization_id)

)) uom,

msib.attribute3 product_type

FROM gme_batch_header gbh,

org_organization_definitions ood,

mtl_system_items_b msib,

gme_material_details gmd,

fnd_lookup_values flv

-- mtl_item_categories mic,

-- mtl_categories_b_kfv mcb,

-- mtl_category_sets_tl mcs

WHERE 1 = 1

AND gbh.organization_id = ood.organization_id

AND gmd.organization_id = msib.organization_id

AND gmd.inventory_item_id = msib.inventory_item_id

AND gbh.batch_id = gmd.batch_id

AND gmd.line_type = '1'

AND gmd.line_no = 1

AND gbh.batch_status <>

-1

--Cancel Batch not required ---Added by JG

AND flv.lookup_type = 'XXJG_DEPT_SUBDEPT'

AND ood.organization_code = flv.tag

AND NVL (gbh.attribute_category, flv.lookup_code) = flv.lookup_code---Added by JG

AND flv.lookup_code = p_department

AND EXISTS (

SELECT 1

FROM mtl_item_categories_v mic

WHERE mic.category_set_name = 'BEX_PLANNING_CATEGORY'

AND mic.inventory_item_id = msib.inventory_item_id

AND mic.organization_id = msib.organization_id

AND mic.segment1 = flv.lookup_code)

AND gbh.batch_id IN (

SELECT batch_id

FROM gme_batch_steps gbs

WHERE EXISTS (

SELECT 1 --gov.oprn_id, gor.oprn_line_id, gov.oprn_no, gov.oprn_vers ,goa.activity, gor.resources

FROM gmd_operation_resources gor,

gmd_operation_activities goa,

gmd_operations_vl gov

WHERE gov.oprn_id = goa.oprn_id

AND gor.oprn_line_id = goa.oprn_line_id

AND gov.oprn_id = gbs.oprn_id

AND gor.resources = NVL (p_resource, gor.resources )))

-- AND gbh.batch_status != -1

-- AND msib.inventory_item_id = mic.inventory_item_id

-- AND msib.organization_id = mic.organization_id

-- AND mcb.category_id = mic.category_id

-- AND mcs.category_set_name = 'Inventory'

-- AND mic.category_set_id = mcs.category_set_id

---- AND msib.segment1 = '1020000292'

AND gbh.organization_id = NVL (p_inv_org, gbh.organization_id)

/* AND nvl(trunc(gbh.ACTUAL_START_DATE),trunc(gbh.plan_start_date))

BETWEEN TO_DATE (p_from_date,'YYYY/MM/DD HH24:MI:SS')

AND TO_DATE (p_to_date,'YYYY/MM/DD HH24:MI:SS' )*/

--AND NVL(TRUNC(gbh.actual_start_date),TRUNC(gbh.plan_start_date))

----Added by JG

AND DECODE (p_department,

'LNP', NVL (TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL1', NVL (TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL2', NVL (TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

)

---,NVL(TRUNC(gbh.actual_start_date),TRUNC(TO_DATE(gbh.attribute1,'YYYY/MM/DD HH24:MI:SS'))) ) -- Change on 9-Mar-2020 For FG Batch

TRUNC (TO_DATE (gbh.attribute1,

'YYYY/MM/DD HH24:MI:SS'

)

BETWEEN TRUNC (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

)

AND TRUNC (TO_DATE (p_to_date,

'YYYY/MM/DD HH24:MI:SS'

)

AND msib.inventory_item_id = NVL (p_item_code, msib.inventory_item_id)

AND NVL (msib.attribute3, 'NA') = NVL (p_product_type, NVL (msib.attribute3, 'NA') )

GROUP BY msib.segment1,

msib.inventory_item_id,

msib.organization_id,

msib.description,

msib.attribute2,

msib.attribute3,

msib.organization_id,

--TO_CHAR (nvl(trunc(gbh.ACTUAL_START_DATE),trunc(gbh.plan_start_date)), 'MON-YY')

TO_CHAR

(DECODE

(p_department,

'LNP', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL1', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

'SPL2', NVL

(TRUNC (gbh.actual_start_date),

TRUNC (gbh.plan_start_date)

TRUNC (TO_DATE (gbh.attribute1,

'YYYY/MM/DD HH24:MI:SS'

)

'MON-YY'

)) abc

GROUP BY abc.product_code,

abc.item_type,

abc.product_name, ---comment on 8-Sep-2020 abc.act_month, ---Added on 08-Mar-2020

abc.dosage_type,

uom,

abc.product_type

ORDER BY abc.product_code;

lv_organization_name VARCHAR2 (3000);

l_full_name VARCHAR2 (3000);

BEGIN

--fnd_file.put_line (fnd_file.LOG,'p_from_date:'||p_from_date);

--fnd_file.put_line (fnd_file.LOG,'p_to_date:' || p_to_date );

BEGIN

SELECT TRIM ( INITCAP (a.title)

|| ' '

|| INITCAP ( a.first_name

|| ' '

|| a.middle_names

|| DECODE (a.middle_names, NULL, '', ' ')

|| a.last_name

)

INTO l_full_name

FROM per_all_people_f a, fnd_user fu

WHERE fu.employee_id = a.person_id AND fu.user_id = p_user_id;

--fnd_profile.value('USER_ID');

fnd_file.put_line (fnd_file.LOG, 'USER_ID:' || p_user_id);

DBMS_OUTPUT.put_line ('P_USER_ID' || p_user_id);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'USER_ID' || ' - ' || SQLERRM);

DBMS_OUTPUT.put_line ('USER_ID' || SQLCODE || ' - ' || SQLERRM);

END;

BEGIN

SELECT organization_name

INTO lv_organization_name

FROM org_organization_definitions

WHERE organization_id = p_inv_org;

fnd_file.put_line (fnd_file.LOG,

'ORGANIZATION_NAME:' || lv_organization_name

);

DBMS_OUTPUT.put_line ('lv_ORGANIZATION_NAME:' || lv_organization_name);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'lv_organization_name' || ' - ' || SQLERRM

);

DBMS_OUTPUT.put_line ( 'lv_organization_name'

|| SQLCODE

|| ' - '

|| SQLERRM

);

END;

BEGIN

fnd_file.put_line (fnd_file.output,

'<?xml version="1.0" encoding="UTF-8"?>'

);

fnd_file.put_line (fnd_file.output, '<XXJG_PRD_WISE_RPT>');

fnd_file.put_line (fnd_file.output,

'<P_FROM_DATE>'

|| TO_DATE (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'DD-MON-YY'

)

|| '</P_FROM_DATE>'

);

fnd_file.put_line (fnd_file.output,

'<P_INV_ORG>'

|| lv_organization_name

|| '</P_INV_ORG>'

);

fnd_file.put_line (fnd_file.output,

'<P_TO_DATE>'

|| TO_DATE (TO_DATE (p_to_date,

'YYYY/MM/DD HH24:MI:SS'

'DD-MON-YY'

)

|| '</P_TO_DATE>'

);

-- fnd_file.put_line (fnd_file.output, '<P_DOSAGE>' || p_dosage || '</P_DOSAGE>' );

fnd_file.put_line (fnd_file.output,

'<P_PRODUCT_TYPE>'

|| p_product_type

|| '</P_PRODUCT_TYPE>'

);

fnd_file.put_line (fnd_file.output,

'<P_ITEM_CODE>' || p_item_code || '</P_ITEM_CODE>'

);

fnd_file.put_line (fnd_file.output,

'<P_DEPARTMENT>'

|| p_department

|| '</P_DEPARTMENT>'

);

fnd_file.put_line (fnd_file.output,

'<P_RESOURCE>' || p_resource || '</P_RESOURCE>'

); ---Added By JG

fnd_file.put_line (fnd_file.output,

'<PRINTED_BY>' || l_full_name || '</PRINTED_BY>'

); ---Added By JG

fnd_file.put_line (fnd_file.output,

'<MONTH1>'

|| TO_CHAR (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH1>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH2>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH2>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH3>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH3>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH4>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH4>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH5>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH5>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH6>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH6>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH7>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH7>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH8>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH8>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH9>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH9>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH10>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH10>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH11>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH11>'

);

fnd_file.put_line

(fnd_file.output,

'<MONTH12>'

|| TO_CHAR

(ADD_MONTHS (TO_DATE (p_from_date,

'YYYY/MM/DD HH24:MI:SS'

'MON-YY'

)

|| '</MONTH12>'

);

FOR i IN cur_main

LOOP

fnd_file.put_line (fnd_file.output, '<XXJG_MAIN_GRP>');

fnd_file.put_line (fnd_file.output,

'<PRODUCT_CODE>'

|| i.product_code

|| '</PRODUCT_CODE>'

);

fnd_file.put_line (fnd_file.output,

'<PRODUCT_NAME>'

|| i.product_name

|| '</PRODUCT_NAME>'

);

fnd_file.put_line (fnd_file.output,

'<ITEM_TYPE>' || i.item_type || '</ITEM_TYPE>'

);

fnd_file.put_line (fnd_file.output,

'<PRODUCT_TYPE>'

|| i.product_type

|| '</PRODUCT_TYPE>'

);

fnd_file.put_line (fnd_file.output,

'<DOSAGE>' || i.dosage_type || '</DOSAGE>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH8>' || i.month8 || '</MONTH8>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH1>' || i.month1 || '</MONTH1>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH2>' || i.month2 || '</MONTH2>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH3>' || i.month3 || '</MONTH3>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH4>' || i.month4 || '</MONTH4>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH5>' || i.month5 || '</MONTH5>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH6>' || i.month6 || '</MONTH6>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH7>' || i.month7 || '</MONTH7>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH9>' || i.month9 || '</MONTH9>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH10>' || i.month10 || '</MONTH10>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH11>' || i.month11 || '</MONTH11>'

);

fnd_file.put_line (fnd_file.output,

'<MONTH12>' || i.month12 || '</MONTH12>'

);

fnd_file.put_line (fnd_file.output,

'<BATCH_SIZE>' || i.batch_size

|| '</BATCH_SIZE>'

);

fnd_file.put_line (fnd_file.output, '<UOM>' || i.uom || '</UOM>');

fnd_file.put_line (fnd_file.output,

'<TT_NO_OF_BATCH>'

|| i.tt_no_of_batch

|| '</TT_NO_OF_BATCH>'

);

fnd_file.put_line (fnd_file.output, '</XXJG_MAIN_GRP>');

---fnd_file.put_line (fnd_file.LOG, 'Item:'||i.product_code);

DBMS_OUTPUT.put_line ('Item:' || i.product_code);

END LOOP;

---fnd_file.put_line (fnd_file.output, 'XXJG_PRD_WISE_RPT XML');

fnd_file.put_line (fnd_file.output, '</XXJG_PRD_WISE_RPT>');

NULL;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'~~~XXJG_PRD_WISE_RPT XML:' || ' - '

|| SQLERRM

);

DBMS_OUTPUT.put_line ( '~~~XXJG_PRD_WISE_RPT XML'

|| SQLCODE

|| ' - '

|| SQLERRM

);

END;

DBMS_OUTPUT.put_line ('XXJG_PRD_WISE_RPT');

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.LOG,

'Exception In Procedure XXJG_prd_wise_rpt_pkg.XXJG_prd_wise_rpt_prc;'

);

fnd_file.put_line (fnd_file.LOG, SQLCODE || ' - ' || SQLERRM);

DBMS_OUTPUT.put_line ( 'XXJG_PRD_WISE_RPT'

|| SQLCODE

|| ' - '

|| SQLERRM

);

END XXJG_PRD_WISE_RPT_PRC;

END XXJG_PRD_WISE_RPT_PKG;