STEP I -- Insert into XYKA_SALARY_API
STEP II -- Call Procedure import_records
-- Records will be populated into xyka_salary_proposal
STEP III -- process_salary_proposal_api ( Main call to hr_maintain_proposal_api.insert_salary_proposal )
CREATE SEQUENCE XYKA_SALARY_PROPOSAL_IDS
START WITH 1000
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
CREATE TABLE XYKA_SALARY_PROPOSAL
(
X_PROPOSAL_ID NUMBER,
X_EMPLOYEE_NUMBER NUMBER,
X_EFFECTIVE_DATE DATE,
X_NET_AMOUNT NUMBER,
X_PROCESS_FLAG CHAR(1 BYTE),
X_APPROVED_FLAG CHAR(1 BYTE),
X_REMARKS VARCHAR2(240 BYTE),
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
);
CREATE TABLE XYKA_SALARY_API
(
X_EMPLOYEE_NUMBER NUMBER,
X_EFFECTIVE_DATE DATE,
X_NET_AMOUNT NUMBER,
X_TRANSFER_DATE DATE,
X_REMARKS VARCHAR2(240 BYTE)
)
select * from XYKA_SALARY_PROPOSAL
--drop table xyka.XYKA_SALARY_PROPOSAL
commit
-------------------------------------------------
select ELEMENT_NAME , REPORTING_NAME , decode (PROCESSING_TYPE ,'R','REC','N','NREC') from PAY_ELEMENT_TYPES_F
select * from PAY_ELEMENT_TYPES_F
CREATE OR REPLACE FORCE VIEW APPS.XYKA_SALARY_PROPOSAL_V
(
SALARY_API_ROWID,
PROPOSAL_ID,
EMPLOYEE_NUMBER,
EMPLOYEE_NAME,
BUSINESS_GROUP_ID,
GRADE,
ASSIGNMENT_ID,
ELEMENT_ENTRY_ID,
DEPARTMENT,
EFFECTIVE_DATE,
PERIOD_NAME,
NET_AMOUNT,
REMARKS,
APPROVED_FLAG,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
AS
SELECT ROWID salary_api_rowid,
xsp.x_proposal_id proposal_id,
xsp.x_employee_number employee_number,
(SELECT last_name
FROM per_people_f
WHERE employee_number = TO_CHAR (xsp.x_employee_number)
AND ROWNUM = 1)
employee_name,
(SELECT business_group_id
FROM per_people_f
WHERE employee_number = TO_CHAR (xsp.x_employee_number)
AND ROWNUM = 1)
business_group_id,
(SELECT pg.NAME
FROM per_people_f ppf, per_assignments_f paf, per_grades pg
WHERE ppf.person_id = paf.person_id
AND paf.grade_id = pg.grade_id
AND xsp.x_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND xsp.x_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND (pg.date_to IS NULL
OR xsp.x_effective_date BETWEEN pg.date_from
AND pg.date_to)
AND ppf.employee_number =
TO_CHAR (xsp.x_employee_number))
grade,
(SELECT paf.assignment_id
FROM per_people_f ppf, per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND xsp.x_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND xsp.x_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppf.employee_number =
TO_CHAR (xsp.x_employee_number))
assignment_id,
(SELECT element_entry_id
FROM pay_element_entries_f
WHERE assignment_id =
(SELECT paf.assignment_id
FROM per_people_f ppf, per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND xsp.x_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND xsp.x_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppf.employee_number =
TO_CHAR (xsp.x_employee_number))
AND SYSDATE BETWEEN effective_start_date
AND effective_end_date
AND element_type_id =
(SELECT element_type_id
FROM pay_element_types_f_tl
WHERE element_name = 'Basic'
AND element_type_id = 92)) --change element as per requirement
element_entry_id,
(SELECT NAME
FROM hr_all_organization_units_tl
WHERE organization_id =
(SELECT organization_id
FROM per_people_f ppf, per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND xsp.x_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND xsp.x_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppf.employee_number =
TO_CHAR (xsp.x_employee_number)))
department,
xsp.x_effective_date effective_date,
TO_CHAR (xsp.x_effective_date, 'MON-RR') period_name,
xsp.x_net_amount net_amount,
xsp.x_remarks remarks,
xsp.x_approved_flag approved_flag,
xsp.x_process_flag process_flag,
xsp.creation_date,
xsp.created_by,
xsp.last_update_date,
xsp.last_updated_by,
xsp.last_update_login
FROM xyka_salary_proposal xsp;
---------------------------------------------------------------------
CREATE OR REPLACE PACKAGE APPS.xyka_salary_proposal_api
AS
--## ##################################################################
--#
--# Script : xyka_salary_api.sql
--# Author : Rushikesh Ranade
--# System : YK Almoayyed and Sons
--# Date : 12 Jul 2012 11:54
--# Version : 1.0
--# Description : Customized Package to process HR Salary APIs
--#
--# Modif Hist :
--# -------------
--#
--# Usage Instructions:
--# -------------------
--# The Objects has to be called from the Salary API Form and Salary
--# API Requests
--#
--## ##################################################################
/* Package Objects Definition */
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- l_status specifies the Process Status
-- L_message specifies the processing messages
-- ------------------
PROCEDURE import_records (
p_out1 OUT VARCHAR2,
p_out2 OUT VARCHAR2,
p_period IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER
);
-- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2);
END xyka_salary_proposal_api;
--------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.xyka_salary_proposal_api
AS
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- ------------------
PROCEDURE import_records (
p_out1 OUT VARCHAR2,
p_out2 OUT VARCHAR2,
p_period IN VARCHAR2,
p_user_id IN NUMBER,
p_login_id IN NUMBER
)
IS
/* Local Variables */
l_success_count NUMBER := 0;
l_rec_count NUMBER := 0;
l_status VARCHAR2 (20);
l_message VARCHAR2 (240);
CURSOR salary_api (c_period_name IN VARCHAR2)
IS
SELECT x_employee_number, x_effective_date, x_net_amount
FROM xyka_salary_api
WHERE TO_CHAR (x_effective_date, 'MON-RR') = c_period_name
AND x_transfer_date IS NULL;
BEGIN
/* Transfers the records into the Salary API Tables */
fnd_file.put_line
(fnd_file.output,
'YKA - Upload Salary Proposal for a Period (XYKASALP) Started on : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output, ' ');
FOR r IN salary_api (p_period)
LOOP
fnd_file.put_line (fnd_file.output,
'Processing for Employee Number # '
|| r.x_employee_number
);
/* Re-Sets the Variables */
l_status := NULL;
l_message := NULL;
l_rec_count := l_rec_count + 1;
/* Start populating the Salary API Main table */
BEGIN
INSERT INTO xyka_salary_proposal
(x_proposal_id,
x_employee_number, x_effective_date,
x_net_amount, x_process_flag, x_approved_flag,
x_remarks, creation_date, created_by,
last_update_date, last_updated_by, last_update_login
)
VALUES (xyka_salary_proposal_ids.NEXTVAL,
r.x_employee_number, r.x_effective_date,
r.x_net_amount, 'N', 'N',
NULL, SYSDATE, p_user_id,
SYSDATE, p_user_id, p_login_id
);
EXCEPTION
WHEN OTHERS
THEN
l_status := 'ERROR';
l_message := SQLERRM;
fnd_file.put_line (fnd_file.output,
' *** Error Message *** : ' || SQLERRM
);
END;
/* If any error occured while transferring the record */
IF l_status = 'ERROR'
THEN
/* Updates the Base Table with the error message */
UPDATE xyka_salary_api
SET x_remarks = l_message
WHERE x_employee_number = r.x_employee_number
AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
ELSIF l_status IS NULL
THEN
l_success_count := l_success_count + 1;
/* If no processing errors occurred then, updates the Transfer Date */
UPDATE xyka_salary_api
SET x_remarks = 'TRANSFERRED',
x_transfer_date = SYSDATE
WHERE x_employee_number = r.x_employee_number
AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
END IF;
/* Commits the Changes */
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output,
'Total Records processed by the Upload program : '
|| l_rec_count
);
fnd_file.put_line (fnd_file.output,
'Total Records Uploaded by the Upload program : '
|| l_success_count
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'YKA - Upload Salary Proposal for a Period (XYKASALP) Completed on : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
);
fnd_file.put_line
(fnd_file.output,
'-----------------------------------------------------------------------------'
);
END;
-- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2)
IS
CURSOR salary_api (c_period_name IN VARCHAR2)
IS
SELECT proposal_id, employee_number, net_amount, effective_date,
business_group_id, assignment_id, element_entry_id
FROM xyka_salary_proposal_v
WHERE TO_CHAR (effective_date, 'MON-RR') = c_period_name
AND approved_flag = 'Y'
AND process_flag = 'N';
/* Local Variables */
l_row_id VARCHAR2 (30);
l_pay_proposal_id NUMBER;
l_element_entry_id NUMBER;
l_ovn NUMBER;
l_inv_next_sal_date_warning BOOLEAN;
l_inv_next_perf_date_warning BOOLEAN;
l_proposed_salary_warning BOOLEAN;
l_approved_warning BOOLEAN;
l_payroll_warning BOOLEAN;
l_dummy BOOLEAN;
l_approved VARCHAR2 (1);
l_status VARCHAR2 (20);
l_message VARCHAR2 (240);
BEGIN
FOR r IN salary_api (p_period_name)
LOOP
l_status := NULL;
l_message := NULL;
l_element_entry_id:=NULL;
BEGIN
/* Start processing the API */
hr_maintain_proposal_api.insert_salary_proposal
(p_pay_proposal_id => l_pay_proposal_id,
p_assignment_id => r.assignment_id,
p_business_group_id => r.business_group_id,
p_change_date => r.effective_date,
p_comments => NULL,
p_next_sal_review_date => NULL,
p_proposal_reason => NULL,
p_proposed_salary_n => r.net_amount,
p_forced_ranking => NULL,
p_performance_review_id => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_object_version_number => l_ovn,
p_multiple_components => 'N',
p_approved => 'Y',
p_validate => FALSE,
p_element_entry_id => r.element_entry_id,
p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
p_proposed_salary_warning => l_proposed_salary_warning,
p_approved_warning => l_approved_warning,
p_payroll_warning => l_payroll_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
/* Catches the Exceptions */
l_message := SQLERRM;
l_status := 'ERROR';
END;
/* If any error occured then */
IF l_status IS NOT NULL
THEN
UPDATE xyka_salary_proposal
SET x_remarks = l_message,
x_approved_flag = 'N',
x_process_flag = 'N'
WHERE x_proposal_id = r.proposal_id;
COMMIT;
/* If the proposal is processed then */
ELSIF l_status IS NULL
THEN
UPDATE xyka_salary_proposal
SET x_remarks = 'PROCESSED',
x_process_flag = 'Y'
WHERE x_proposal_id = r.proposal_id;
COMMIT;
END IF;
END LOOP;
END process_salary_proposal_api;
END xyka_salary_proposal_api;
/
------------------ Execute --------------
INSERT INTO XYKA_SALARY_API (X_EMPLOYEE_NUMBER,X_EFFECTIVE_DATE,X_NET_AMOUNT,X_TRANSFER_DATE,X_REMARKS)VALUES(14699,TO_DATE (SYSDATE - 14, 'DD-MM-YY'),1200,NULL,'5% salary hike JUL-12');
select to_date(sysdate-14,'DD-MM-YY') from dual
commit
select * from XYKA_SALARY_API
delete from XYKA_SALARY_API
--edit XYKA_SALARY_API
SELECT x_employee_number, x_effective_date, x_net_amount
FROM xyka_salary_api
WHERE TO_CHAR (x_effective_date, 'MON-RR') = :c_period_name
AND x_transfer_date IS NULL
select * from fnd_user where user_name = '99999'
------------------ Procedure 1 -----------------------
DECLARE
p_out1 VARCHAR2 (240);
p_out2 VARCHAR2 (240);
p_period VARCHAR2 (10) := 'JUL-12';
p_user_id number := 1295;
p_login_id number := 1295;
BEGIN
xyka_salary_proposal_api.import_records (p_out1,
p_out2,
p_period,
p_user_id,
p_login_id);
END;
commit
-- Record gets added into
select * from xyka_salary_proposal
delete from xyka_salary_proposal where X_APPROVED_FLAG = 'Y'
-- on success x_remark gets updated to TRANSFERRED and trnsfer_date to SYSDATE
select * from xyka_salary_api
UPDATE xyka_salary_proposal
SET X_APPROVED_FLAG = 'Y' ,
X_EFFECTIVE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
LAST_UPDATE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
CREATION_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY')
--WHERE X_EMPLOYEE_NUMBER = 14699
commit ;
select * from xyka_salary_proposal
--delete from xyka_salary_proposal where X_REMARKS like 'ORA-20001: This date already exists. Please enter a new date.'
SELECT proposal_id, employee_number, net_amount, effective_date,
business_group_id, assignment_id, element_entry_id
FROM xyka_salary_proposal_v
WHERE TO_CHAR (effective_date, 'MON-RR') = :c_period_name
AND approved_flag = 'Y'
AND process_flag = 'N';
-- call process_salary_proposal_api
DECLARE
p_period_name VARCHAR2 (10) := 'JUL-22';
BEGIN
xyka_salary_proposal_api.process_salary_proposal_api (p_period_name);
END;
COMMIT
/* If any error occured then */
x_approved_flag = 'N',
x_process_flag = 'N'
ORA-20001: The effective date for all DateTracked operations must not include a time component.
/* If the proposal is processed then */
x_remarks = 'PROCESSED',
x_process_flag = 'Y'
select * from xyka_salary_proposal
select * from XYKA_SALARY_PROPOSAL_V
Creating a blog is not just a hobby it’s a partnership to grow together.
Saturday, July 28, 2012
Tuesday, July 10, 2012
PO - Action History
********** PO - Action History **********
SELECT pah.object_id,
pah.object_type_code,
pah.object_sub_type_code,
pah.sequence_num,
pah.last_update_date,
pah.last_updated_by,
pah.creation_date,
pah.created_by,
pah.action_code,
plc1.displayed_field Action,
pah.action_date,
pah.employee_id,
hr.full_name Employee,
pah.approval_path_id,
pah.note,
pah.object_revision_num,
pah.offline_code,
pah.last_update_login,
pah.request_id,
pah.program_application_id,
pah.program_id,
pah.program_update_date,
pah.program_date,
pah.approval_group_id
FROM po_action_history pah,
po_lookup_codes plc1,
po_lookup_codes plc2,
per_all_people_f hr
WHERE pah.object_id = 176977 -- po_header_id
AND pah.object_type_code <> 'RELEASE'
AND pah.object_sub_type_code = 'STANDARD'
AND hr.person_id = pah.employee_id
AND pah.action_date BETWEEN hr.effective_start_date
AND hr.effective_end_date
AND plc1.lookup_code(+) = pah.action_code
AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
AND plc2.lookup_code(+) = pah.action_code
AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY pah.sequence_num
********* PO - Approval Hierarchy *******
SELECT det.lev "Level",
det.subordinate_POSITION_ID "Subordinate Position Id",
det.NAME "Name",
DECODE (
(SELECT COUNT ( * ) cnt
FROM PER_ALL_ASSIGNMENTS_F paa
WHERE paa.position_id = det.subordinate_POSITION_ID
AND SYSDATE BETWEEN paa.effective_start_date
AND NVL (paa.effective_end_date,
SYSDATE)),
1,
(SELECT full_name
FROM PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
WHERE paa.position_id = det.subordinate_POSITION_ID
AND SYSDATE BETWEEN paa.effective_start_date
AND NVL (paa.effective_end_date,
SYSDATE)
AND SYSDATE BETWEEN pp.effective_start_date
AND NVL (pp.effective_end_date,
SYSDATE)
AND paa.PERSON_ID = pp.person_id),
'Multiple Employees attached to this Position'
)
Employee_Name
FROM (SELECT ps.lev, PS.subordinate_POSITION_ID, pos.NAME
FROM ( SELECT LEVEL lev, PS.subordinate_POSITION_ID
FROM per_pos_structure_elements PS
START WITH PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
AND PS.SUBORDINATE_POSITION_ID = 4087
CONNECT BY PRIOR PS.PARENT_POSITION_ID =
PS.SUBORDINATE_POSITION_ID
AND PS.POS_STRUCTURE_VERSION_ID =
NVL (62, 0)) ps,
per_positions POS
WHERE ps.subordinate_POSITION_ID = pos.POSITION_ID) det
********* PO - AP *********
SELECT ih.invoice_num "Invoice Num",
pla.line_num "PO Line Num",
plla.shipment_num "PO Shipment Num",
plla.quantity_billed "Shipment Qty Billed"
FROM ap_invoices ih,
ap_invoice_distributions id,
po_headers ph,
po_distributions pd,
po_lines pla,
po_line_locations plla
WHERE ih.invoice_id(+) = id.invoice_id
AND id.po_distribution_id(+) = pd.po_distribution_id
AND ph.po_header_id = pd.po_header_id
AND ph.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pd.line_location_id
AND ih.invoice_num IS NOT NULL
AND NVL (plla.po_release_id, 0) = 0
AND ph.po_header_id = 176977
SELECT pah.object_id,
pah.object_type_code,
pah.object_sub_type_code,
pah.sequence_num,
pah.last_update_date,
pah.last_updated_by,
pah.creation_date,
pah.created_by,
pah.action_code,
plc1.displayed_field Action,
pah.action_date,
pah.employee_id,
hr.full_name Employee,
pah.approval_path_id,
pah.note,
pah.object_revision_num,
pah.offline_code,
pah.last_update_login,
pah.request_id,
pah.program_application_id,
pah.program_id,
pah.program_update_date,
pah.program_date,
pah.approval_group_id
FROM po_action_history pah,
po_lookup_codes plc1,
po_lookup_codes plc2,
per_all_people_f hr
WHERE pah.object_id = 176977 -- po_header_id
AND pah.object_type_code <> 'RELEASE'
AND pah.object_sub_type_code = 'STANDARD'
AND hr.person_id = pah.employee_id
AND pah.action_date BETWEEN hr.effective_start_date
AND hr.effective_end_date
AND plc1.lookup_code(+) = pah.action_code
AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
AND plc2.lookup_code(+) = pah.action_code
AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY pah.sequence_num
********* PO - Approval Hierarchy *******
SELECT det.lev "Level",
det.subordinate_POSITION_ID "Subordinate Position Id",
det.NAME "Name",
DECODE (
(SELECT COUNT ( * ) cnt
FROM PER_ALL_ASSIGNMENTS_F paa
WHERE paa.position_id = det.subordinate_POSITION_ID
AND SYSDATE BETWEEN paa.effective_start_date
AND NVL (paa.effective_end_date,
SYSDATE)),
1,
(SELECT full_name
FROM PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
WHERE paa.position_id = det.subordinate_POSITION_ID
AND SYSDATE BETWEEN paa.effective_start_date
AND NVL (paa.effective_end_date,
SYSDATE)
AND SYSDATE BETWEEN pp.effective_start_date
AND NVL (pp.effective_end_date,
SYSDATE)
AND paa.PERSON_ID = pp.person_id),
'Multiple Employees attached to this Position'
)
Employee_Name
FROM (SELECT ps.lev, PS.subordinate_POSITION_ID, pos.NAME
FROM ( SELECT LEVEL lev, PS.subordinate_POSITION_ID
FROM per_pos_structure_elements PS
START WITH PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
AND PS.SUBORDINATE_POSITION_ID = 4087
CONNECT BY PRIOR PS.PARENT_POSITION_ID =
PS.SUBORDINATE_POSITION_ID
AND PS.POS_STRUCTURE_VERSION_ID =
NVL (62, 0)) ps,
per_positions POS
WHERE ps.subordinate_POSITION_ID = pos.POSITION_ID) det
********* PO - AP *********
SELECT ih.invoice_num "Invoice Num",
pla.line_num "PO Line Num",
plla.shipment_num "PO Shipment Num",
plla.quantity_billed "Shipment Qty Billed"
FROM ap_invoices ih,
ap_invoice_distributions id,
po_headers ph,
po_distributions pd,
po_lines pla,
po_line_locations plla
WHERE ih.invoice_id(+) = id.invoice_id
AND id.po_distribution_id(+) = pd.po_distribution_id
AND ph.po_header_id = pd.po_header_id
AND ph.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND plla.line_location_id = pd.line_location_id
AND ih.invoice_num IS NOT NULL
AND NVL (plla.po_release_id, 0) = 0
AND ph.po_header_id = 176977
Monday, June 18, 2012
R12 BANK TABLE AND QUERY
R12 BANK TABLE AND QUERY
SELECT HOU.NAME OPERATING_UNIT,
BNK.BANK_ACCOUNT_NUM,
BRNCH.BANK_NAME,
BRNCH.BANK_BRANCH_TYPE,
BRNCH.BANK_BRANCH_NAME,
BRNCH.DESCRIPTION
FROM CE_BANK_ACCOUNTS BNK,
CE_BANK_ACCT_USES_ALL SITE,
CEFV_BANK_BRANCHES BRNCH,
HR_OPERATING_UNITS HOU
WHERE BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
AND HOU.ORGANIZATION_ID = SITE.ORG_ID
==========================================================================
SELECT HOU.NAME OPERATING_UNIT,
sob.SHORT_NAME sob_name,
BRNCH.BANK_NAME,
BRNCH.BANK_BRANCH_NAME,
BNK.DESCRIPTION,
BRNCH.ADDRESS_LINE1,
BRNCH.ADDRESS_LINE2,
BRNCH.ADDRESS_LINE3,
BRNCH.ADDRESS_LINE4,
BRNCH.CITY,
BRNCH.STATE,
BRNCH.ZIP_CODE,
BRNCH.COUNTRY,
BNK.BANK_ACCOUNT_NAME,
BNK.BANK_ACCOUNT_TYPE,
BNK.BANK_ACCOUNT_NUM,
BNK.MULTI_CURRENCY_ALLOWED_FLAG,
BNK.MULTI_CURRENCY_ALLOWED_FLAG,
BNK.ACCOUNT_CLASSIFICATION,
BNK.ZERO_AMOUNT_ALLOWED,
BNK.MAX_CHECK_AMOUNT,
BNK.RECEIPT_MULTI_CURRENCY_FLAG,
BNK.CURRENCY_CODE,
BNK.ACCOUNT_HOLDER_NAME,
gcf.CONCATENATED_SEGMENTS
FROM CE_BANK_ACCOUNTS BNK,
CE_BANK_ACCT_USES_ALL SITE,
CEFV_BANK_BRANCHES BRNCH,
HR_OPERATING_UNITS HOU,
gl_sets_of_books sob,
gl_code_combinations_kfv gcf
WHERE BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
AND HOU.ORGANIZATION_ID = SITE.ORG_ID
AND sob.SET_OF_BOOKS_ID = HOU.SET_OF_BOOKS_ID
AND BNK.ASSET_CODE_COMBINATION_ID = gcf.CODE_COMBINATION_ID
BNK.BANK_ACCOUNT_NUM,
BRNCH.BANK_NAME,
BRNCH.BANK_BRANCH_TYPE,
BRNCH.BANK_BRANCH_NAME,
BRNCH.DESCRIPTION
FROM CE_BANK_ACCOUNTS BNK,
CE_BANK_ACCT_USES_ALL SITE,
CEFV_BANK_BRANCHES BRNCH,
HR_OPERATING_UNITS HOU
WHERE BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
AND HOU.ORGANIZATION_ID = SITE.ORG_ID
==========================================================================
SELECT HOU.NAME OPERATING_UNIT,
sob.SHORT_NAME sob_name,
BRNCH.BANK_NAME,
BRNCH.BANK_BRANCH_NAME,
BNK.DESCRIPTION,
BRNCH.ADDRESS_LINE1,
BRNCH.ADDRESS_LINE2,
BRNCH.ADDRESS_LINE3,
BRNCH.ADDRESS_LINE4,
BRNCH.CITY,
BRNCH.STATE,
BRNCH.ZIP_CODE,
BRNCH.COUNTRY,
BNK.BANK_ACCOUNT_NAME,
BNK.BANK_ACCOUNT_TYPE,
BNK.BANK_ACCOUNT_NUM,
BNK.MULTI_CURRENCY_ALLOWED_FLAG,
BNK.MULTI_CURRENCY_ALLOWED_FLAG,
BNK.ACCOUNT_CLASSIFICATION,
BNK.ZERO_AMOUNT_ALLOWED,
BNK.MAX_CHECK_AMOUNT,
BNK.RECEIPT_MULTI_CURRENCY_FLAG,
BNK.CURRENCY_CODE,
BNK.ACCOUNT_HOLDER_NAME,
gcf.CONCATENATED_SEGMENTS
FROM CE_BANK_ACCOUNTS BNK,
CE_BANK_ACCT_USES_ALL SITE,
CEFV_BANK_BRANCHES BRNCH,
HR_OPERATING_UNITS HOU,
gl_sets_of_books sob,
gl_code_combinations_kfv gcf
WHERE BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
AND HOU.ORGANIZATION_ID = SITE.ORG_ID
AND sob.SET_OF_BOOKS_ID = HOU.SET_OF_BOOKS_ID
AND BNK.ASSET_CODE_COMBINATION_ID = gcf.CODE_COMBINATION_ID
Wednesday, June 13, 2012
PO AP R12
SELECT PO_DISTRIBUTION_ID,SUM(AMOUNT),SUM(QUANTITY_INVOICED)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
GROUP BY PO_DISTRIBUTION_ID
HAVING PO_DISTRIBUTION_ID IN
(
SELECT DISTINCT(PO_DISTRIBUTION_ID) FROM
AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = &INVOICE_ID
)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
GROUP BY PO_DISTRIBUTION_ID
HAVING PO_DISTRIBUTION_ID IN
(
SELECT DISTINCT(PO_DISTRIBUTION_ID) FROM
AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = &INVOICE_ID
)
Monday, April 16, 2012
Inventory - Onhand , Transaction Quantity , Reserve Quantity
-------------------------------------------------------------------------------------------------------------------
create table xyka_test_todrop_bkp as ( select * from xyka_test_todrop)
commit
drop table xyka_test_todrop
create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM
alter table xyka_test_todrop
add( qty_transact number,
qty_reserve number );
select * from xyka_test_todrop
--where item = 'BH-28'
select * from hr_operating_units
DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
from xyka_test_todrop
--where INVENTORY_ITEM_ID = 8367
--and LOCATOR_ID = 18471
--and ORGANIZATION_ID = 112
--and SUBINVENTORY_CODE = 'IBS'
)
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update xyka_test_todrop
set QTY_TRANSACT = X_att,
qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
and LOCATOR_ID = x.LOCATOR_ID
and ORGANIZATION_ID = 105
and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;
end loop;
dbms_output.put_line(to_char(b));
END;
select * from xyka_test_todrop
commit
create table xyka_test_todrop_bkp as ( select * from xyka_test_todrop)
commit
drop table xyka_test_todrop
create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM
alter table xyka_test_todrop
add( qty_transact number,
qty_reserve number );
select * from xyka_test_todrop
--where item = 'BH-28'
select * from hr_operating_units
DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
from xyka_test_todrop
--where INVENTORY_ITEM_ID = 8367
--and LOCATOR_ID = 18471
--and ORGANIZATION_ID = 112
--and SUBINVENTORY_CODE = 'IBS'
)
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update xyka_test_todrop
set QTY_TRANSACT = X_att,
qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
and LOCATOR_ID = x.LOCATOR_ID
and ORGANIZATION_ID = 105
and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;
end loop;
dbms_output.put_line(to_char(b));
END;
select * from xyka_test_todrop
commit
Thursday, April 12, 2012
XML Reports An invalid character was found in text content.
You need to update your profile in 11i / R12 .
change this to UTF8. We had this issue with our international documents .
Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8
FND: NATIVE CLIENT ENCODING
Before >>
After >>
change this to UTF8. We had this issue with our international documents .
Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8
FND: NATIVE CLIENT ENCODING
Before >>
Monday, April 9, 2012
WMS - Pick Pending Summery Report
/* Formatted on 4/9/2012 3:12:18 PM (QP5 v5.114.809.3010) */
SELECT ROW_NUMBER ()
OVER (
ORDER BY
oel.shipping_method_code, hp.party_name, oeh.order_number
)
AS sr_no,
mmtt.transaction_temp_id task_no,
wdt.task_id,
hp.party_name customer,
msi.DESCRIPTION,
flv.meaning task_status,
fu1.user_name USER_name,
mmtt.transaction_quantity QUANTITY,
oeh.order_number,
mmtt.SUBINVENTORY_CODE,
mmtt.TRANSACTION_SOURCE_NAME,
wt.user_task_type_code EQUIPMENT,
mmtt.LOCATOR_ID,
oel.shipping_method_code route,
ot.name order_type
FROM hz_parties hp,
hz_cust_accounts hca,
oe_order_headers_all oeh,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,
oe_order_lines_all oel,
fnd_lookup_values flv,
fnd_user fu1,
mtl_system_items_b msi,
oe_transaction_types_tl ot,
wms_tasks_v wt
WHERE msi.organization_id = mmtt.organization_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND oel.line_id = mmtt.trx_source_line_id
AND flv.lookup_type = 'WMS_TASK_STATUS'
AND flv.lookup_code =
NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
AND flv.language = 'US'
AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND oeh.header_id = oel.header_id
AND fu1.employee_id(+) = wdt.person_id
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND flv.MEANING IN ('Pending', 'Unreleased')
AND oeh.order_type_id = ot.transaction_type_id
AND wt.transaction_temp_id = mmtt.transaction_temp_id
AND hp.party_name = NVL (:P_CUST, hp.party_name)
AND wt.user_task_type_code =
NVL (:P_EQUIPMENT, wt.user_task_type_code)
AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
AND ot.name = NVL (:P_ORD_TYP, ot.name)
AND ot.language = 'US'
AND NVL (oel.shipping_method_code, -99) =
NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
AND mmtt.SUBINVENTORY_CODE =
NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
:P_FRM_DT,
mmtt.creation_date
))
AND TRUNC(NVL (
:P_TO_DT,
mmtt.creation_date
))
AND mmtt.organization_id =
NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY oel.shipping_method_code, hp.party_name, oeh.order_number
SELECT ROW_NUMBER ()
OVER (
ORDER BY
oel.shipping_method_code, hp.party_name, oeh.order_number
)
AS sr_no,
mmtt.transaction_temp_id task_no,
wdt.task_id,
hp.party_name customer,
msi.DESCRIPTION,
flv.meaning task_status,
fu1.user_name USER_name,
mmtt.transaction_quantity QUANTITY,
oeh.order_number,
mmtt.SUBINVENTORY_CODE,
mmtt.TRANSACTION_SOURCE_NAME,
wt.user_task_type_code EQUIPMENT,
mmtt.LOCATOR_ID,
oel.shipping_method_code route,
ot.name order_type
FROM hz_parties hp,
hz_cust_accounts hca,
oe_order_headers_all oeh,
mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,
oe_order_lines_all oel,
fnd_lookup_values flv,
fnd_user fu1,
mtl_system_items_b msi,
oe_transaction_types_tl ot,
wms_tasks_v wt
WHERE msi.organization_id = mmtt.organization_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND oel.line_id = mmtt.trx_source_line_id
AND flv.lookup_type = 'WMS_TASK_STATUS'
AND flv.lookup_code =
NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
AND flv.language = 'US'
AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
AND oeh.header_id = oel.header_id
AND fu1.employee_id(+) = wdt.person_id
AND oeh.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND flv.MEANING IN ('Pending', 'Unreleased')
AND oeh.order_type_id = ot.transaction_type_id
AND wt.transaction_temp_id = mmtt.transaction_temp_id
AND hp.party_name = NVL (:P_CUST, hp.party_name)
AND wt.user_task_type_code =
NVL (:P_EQUIPMENT, wt.user_task_type_code)
AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
AND ot.name = NVL (:P_ORD_TYP, ot.name)
AND ot.language = 'US'
AND NVL (oel.shipping_method_code, -99) =
NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
AND mmtt.SUBINVENTORY_CODE =
NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
:P_FRM_DT,
mmtt.creation_date
))
AND TRUNC(NVL (
:P_TO_DT,
mmtt.creation_date
))
AND mmtt.organization_id =
NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY oel.shipping_method_code, hp.party_name, oeh.order_number
Subscribe to:
Posts (Atom)

