[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE pm_insert_pti_pvt
(p_transaction_source VARCHAR2,
p_batch_name VARCHAR2,
p_expenditure_ending_date DATE,
p_employee_number VARCHAR2,
p_organization_name VARCHAR2,
p_expenditure_item_date DATE,
p_project_number VARCHAR2,
p_task_number VARCHAR2,
p_expenditure_type VARCHAR2,
p_pa_quantity NUMBER,
p_raw_cost NUMBER,
p_expenditure_comment VARCHAR2,
p_orig_transaction_reference VARCHAR2,
p_raw_cost_rate NUMBER,
p_unmatched_negative_txn_flag VARCHAR2,
p_gl_date DATE,
p_org_id NUMBER,
p_burdened_cost NUMBER,
p_burdened_cost_rate NUMBER,
p_system_linkage VARCHAR2,
p_transaction_status_code VARCHAR2,
p_denom_currency_code VARCHAR2,
p_transaction_id NUMBER,
p_transaction_action_id NUMBER,
p_transaction_source_type_id NUMBER,
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_cost_element_id NUMBER,
p_resource_id NUMBER,
p_source_flag NUMBER,
p_variance_flag NUMBER,
p_primary_quantity NUMBER,
p_transfer_organization_id NUMBER,
p_fob_point NUMBER,
p_wip_entity_id NUMBER,
p_basis_resource NUMBER,
p_type_class NUMBER,
p_project_id NUMBER,
p_task_id NUMBER,
p_transaction_date DATE,
p_cost_group_id NUMBER,
p_transfer_cost_group_id NUMBER,
p_transaction_source_id NUMBER,
p_to_project_id NUMBER,
p_to_task_id NUMBER,
p_source_project_id NUMBER,
p_source_task_id NUMBER,
p_transfer_transaction_id NUMBER,
p_primary_cost_method NUMBER,
p_acct_period_id NUMBER,
p_exp_org_id NUMBER,
p_distribution_account_id NUMBER,
p_proj_job_ind NUMBER,
p_first_matl_se_exp_type VARCHAR2,
p_inv_txn_source_literal VARCHAR2,
p_cap_txn_source_literal VARCHAR2,
p_inv_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_user_def_exp_type VARCHAR2,
p_flow_schedule VARCHAR2,
p_si_asset_yes_no NUMBER,
p_transfer_si_asset_yes_no NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2
);
| For MMT trasactions, all transactions that are not selected by the view |
| 'cst_pm_matl_txn_v' and satisfy the date and org criteria are marked as |
| cost collected with no exception. |
| |
| For WIP trasactions, transactions falling into either of these category |
| are marked as cost collected. |
| -- All wip transaction records that refer to a non-project costed job |
| -- All wt records with transaction type as other than (1,2,3) and refer |
| to a project costed job |
| -- All wt records with transaction type as (1,2,3) but have no records |
| in WTA because their standard_rate_flag was 2 and auto_charge_type as|
| any thing other than manual resulting in the resource having no cost.|
| -- All ipv transfer transactions (source_code = 'IPV' |
| |
| PARAMETERS |
| Organization_Id, |
| UpToDate, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| O_err_num, |
| O_err_code, |
| O_err_msg */
PROCEDURE pm_mark_non_project_world_txns (
p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
l_err_num NUMBER;
SELECT mp.primary_cost_method
INTO l_primary_cost_method
FROM mtl_parameters mp
WHERE mp.organization_id = p_org_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE NOT EXISTS
( SELECT NULL
FROM mtl_transaction_types mtt
WHERE mtt.type_class = 1
AND mtt.transaction_type_id = mmt.transaction_type_id )
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is NULL
AND mmt.pm_cost_collected = 'N'
AND mmt.transaction_action_id <> 17; -- See Note about exception above
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE NOT EXISTS
( SELECT NULL
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.transaction_id = mmt.transaction_id )
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is NULL
AND mmt.pm_cost_collected = 'N';
UPDATE
mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE
(
mmt.organization_id <> nvl(mmt.owning_organization_id, mmt.organization_id)
OR nvl(mmt.owning_tp_type, 2) <> 2
)
AND mmt.pm_cost_collected = 'N'
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is null;
/* cost collected. Retroactive price updates will laso be set as cost */
/* collected */
UPDATE
mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.pm_cost_collected = 'N'
AND mmt.organization_id = p_org_id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is null
AND
(
(
(
NVL(mmt.logical_transaction,2) = 1
)
AND NOT ( MMT.TRANSACTION_TYPE_ID = 19
AND MMT.TRANSACTION_ACTION_ID = 26
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 1
AND NVL(MMT.LOGICAL_TRX_TYPE_CODE,5) = 2
AND EXISTS
(
SELECT
1
FROM rcv_transactions rcv
WHERE rcv.transaction_id = NVL(mmt.rcv_transaction_id,-9999)
AND rcv.organization_id = p_org_id
)
)
AND NOT (mmt.transaction_action_id = 17 and mmt.transaction_source_type_id = 7) /*Bug 7120525*/
)
OR NVL(mmt.logical_trx_type_code,5) = 4
);
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Count of logical txns or retroactive price updates in MMT - no cost collection - is #: '||to_char(SQL%ROWCOUNT) );
UPDATE
wip_transactions wt
SET wt.pm_cost_collected = NULL,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.organization_id = p_Org_Id
AND wt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND wt.pm_cost_collected = 'N'
AND
(
(
NOT wt.transaction_type in (1,2,3,17)
AND wt.project_id IS NOT NULL
)
OR wt.project_id IS NULL
OR
(
wt.transaction_type in (1,2,3)
AND NOT EXISTS
(
SELECT
NULL
FROM wip_transaction_accounts wta
WHERE wta.transaction_id = wt.transaction_id
)
)
OR wt.source_code = 'IPV' -- Bug 2130771
);
Bug 14601690: the assembly return transaction has no project_id in MMT, but the job has project_id in WDJ, so it will be selected by cost collection manager,
But if there is no MOH or even no any MTA accounting populated in MTA(like that if the subinv is expense and the job is expense), it will be error,
because cost collection worker will get data from MTA for that txn.
check in MTA, if no MOH was absorbed or not in MTA (in this case MTA doesn't even exists), if none is found mark it as not applicable*/
l_stmt_num := 41;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE NOT EXISTS
( SELECT 1 FROM MTL_TRANSACTION_ACCOUNTS
WHERE TRANSACTION_ID = mmt.TRANSACTION_ID
AND cost_element_id = 2
AND accounting_line_type = 3
)
AND mmt.organization_id = p_Org_Id
AND mmt.transaction_date <= ((trunc(sysdate) - p_prior_days) + 0.99999)
AND mmt.costed_flag is NULL
AND mmt.pm_cost_collected = 'N'
AND mmt.transaction_action_id in (31, 32);
| This procedure would for every record selected from the view, update the|
| MMT record to assign a group_id. The number of records to be updated is |
| determined by the user_spec_group_size. |
| |
| Every Transaction that satisfies the conditions mentioned below gets |
| a group id assigned to itself. |
| |
| - All Project related transactions from the Project World |
| |
| - All Project related transactions from the Non-Proj World |
| - Select Txns: Capital Projects related txns in the NPW |
| - Select Txns: Component Issue to Project Job from a NPW |
| - Select Txns: Component Return from Project Job to NPW |
| |
| - Ensure that the transaction has not yet got any group assigned |
| |
| - Ensure that the transaction took place before the Date upto which the |
| Cost Collection was desired |
| |
| - Ensure that the transaction took place in the Org for which the cost |
| Cost Collection was desired |
| |
| PARAMETERS |
| p_Org_Id, |
| p_prior_days, |
| p_user_spec_group_size, |
| p_rows_processed, |
| p_group_id OUT, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| p_proj_misc_txn_only, |
| O_err_num, |
| O_err_code, |
| O_err_msg |
| |
| HISTORY |
| 07-SEP-96 Bhaskar Dasari Created. |
*----------------------------------------------------------------------------*/
PROCEDURE assign_groups_to_mmt_txns ( p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_spec_group_size NUMBER,
p_rows_processed OUT NOCOPY NUMBER,
p_group_id OUT NOCOPY NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
p_proj_misc_txn_only NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
CURSOR sel_mmt_trx (c_Org_Id NUMBER,
c_prior_days NUMBER,
c_proj_misc_txn_only NUMBER,
c_user_spec_group_size NUMBER) IS
SELECT NULL
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id in (
SELECT cpmtv.transaction_id
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.organization_id = c_Org_Id
AND cpmtv.transaction_date <=
((trunc(sysdate) - c_prior_days) + 0.99999)
AND cpmtv.pm_cost_collector_group_id is NULL
AND rownum <= c_user_spec_group_size
AND ( cpmtv.type_class = decode(c_proj_misc_txn_only,1,1,cpmtv.type_class)
OR cpmtv.transaction_action_id=17)
)
FOR UPDATE OF mmt.pm_cost_collected NOWAIT;
SELECT mtl_material_transactions_s.nextval
INTO p_group_id
FROM dual;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collector_group_id = p_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE current of sel_mmt_trx;
SELECT cpmtv.transaction_action_id,
cpmtv.transaction_source_type_id,
cpmtv.type_class,
cpmtv.expenditure_type,
cpmtv.transaction_date,
cpmtv.project_id,
cpmtv.task_id,
cpmtv.inventory_item_id,
cpmtv.primary_quantity,
cpmtv.costed_flag,
cpmtv.primary_cost_method,
cpmtv.avg_rates_cost_type_id,
cpmtv.item_description,
cpmtv.cost_group_id,
cpmtv.transfer_cost_group_id,
cpmtv.transaction_source_id,
cpmtv.to_project_id,
cpmtv.to_task_id,
cpmtv.source_project_id,
cpmtv.source_task_id,
cpmtv.transfer_transaction_id,
cpmtv.acct_period_id,
cpmtv.pm_cost_collector_group_id,
cpmtv.exp_org_id,
cpmtv.distribution_account_id,
cpmtv.transfer_organization_id,
cpmtv.flow_schedule,
cpmtv.si_asset_yes_no,
cpmtv.transfer_si_asset_yes_no
FROM cst_pm_matl_txn_v cpmtv
WHERE cpmtv.transaction_id = c_Transaction_Id
AND cpmtv.organization_id = c_Organization_Id;
SELECT count(*)
INTO l_count
FROM cst_pm_matl_txn_v
WHERE transaction_id = p_transaction_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE
mmt.transaction_id =p_transaction_id;
/* Bug 5241396.Need to update the pm_cost_collected to NULL only for direct interorg and sub
inventory txfrs and not for intransit receipts that will have a txnfr txn ID */
/* Bug #2623627. Updating group_id for the transfer transaction_id so
the transfer to projects field does not show "Not Applicable" */
If cpmtv_rec.transaction_action_id <> 12 then
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = cpmtv_rec.pm_cost_collector_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id IN (p_transaction_id, cpmtv_rec.transfer_transaction_id);
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = cpmtv_rec.pm_cost_collector_group_id,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id;
has not yet been costed but the sending txn is being cost collected. The sending txn is being updated with a warning but
pm_cost_collected flag is not being updated to error to prevent
user from manually resubmitting the txn for cost collection */
rollback to pm_cc_worker_mmt;
UPDATE mtl_material_transactions mmt
SET
mmt.error_explanation = l_err_msg,
mmt.pm_cost_collector_group_id = null,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id;
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = NULL,
mmt.pm_cost_collector_group_id = NULL,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id =p_transaction_id;
| This procedure would for every record selected from the view, update the|
| WT record to assign a group_id. The number of records to be updated is |
| determined by the user_spec_group_size. Delete all 'WITE' records if any|
| for the transactions that were assigned the group_id. |
| |
| Every Transaction that satisfies the conditions mentioned below gets |
| a group id assigned to itself. |
| |
| - All transactions resulting from a job that is Project related |
| |
| - Select Txns: Resource, O/P and Overhead |
| |
| - Ensure that the transaction has not yet been assigned a group_id |
| |
| - Ensure that the transaction took place before the Date upto which the |
| Cost Collection was desired |
| |
| - Ensure that the transaction took place in Org for which the Cost Coll |
| was desired |
| |
| PARAMETERS |
| p_Org_Id, |
| p_prior_days, |
| p_user_spec_group_size, |
| p_rows_processed, |
| p_group_id OUT, |
| p_user_id, |
| p_login_id, |
| p_req_id, |
| p_prg_appl_id, |
| p_prg_id, |
| O_err_num, |
| O_err_code, |
| O_err_msg |
| |
| HISTORY |
| 07-SEP-96 Bhaskar Dasari Created. |
| |
| 21-NOV-97 Hemant Gosain Modified. Refer Bug# 589460 Regarding |
| PJM Auto Task API. Get Prj/Tsk Ref from Table WT. |
*----------------------------------------------------------------------------*/
PROCEDURE assign_groups_to_wt_txns (
p_Org_Id NUMBER,
p_prior_days NUMBER,
p_user_spec_group_size NUMBER,
p_rows_processed OUT NOCOPY NUMBER,
p_group_id OUT NOCOPY NUMBER,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
CURSOR sel_wt_trx ( c_Org_Id NUMBER,
c_prior_days NUMBER,
c_user_spec_group_size NUMBER) IS
SELECT NULL
FROM wip_transactions wt
WHERE wt.organization_id = c_Org_Id
AND wt.transaction_date <= ((trunc(sysdate) - c_prior_days) + 0.99999)
AND wt.pm_cost_collected = 'N'
AND wt.transaction_type in (1,2,3,17)
AND wt.project_id IS NOT NULL -- Bug #589460
AND wt.pm_cost_collector_group_id is NULL
AND rownum <= c_user_spec_group_size
FOR UPDATE OF wt.pm_cost_collected NOWAIT;
SELECT wip_transactions_S.nextval
INTO l_group_id
FROM dual;
UPDATE wip_transactions wt
SET wt.pm_cost_collector_group_id = l_group_id,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE CURRENT of sel_wt_trx;
DELETE wip_txn_interface_errors wite
WHERE wite.transaction_id in
( SELECT wt.transaction_id
FROM wip_transactions wt
WHERE wt.pm_cost_collector_group_id = l_group_id
AND wt.pm_cost_collected = 'N' )
AND wite.error_column = 'PM_COST_COLLECTED';
| Enhanced Selection criteria to include WTA reference account |
| to support Project Capitalization. |
| |
| 21-NOV-97 Hemant Gosain Modified. |
| Refer Bug# 589460. Get Prj/Tsk form WT Table. |
*----------------------------------------------------------------------------*/
PROCEDURE pm_cc_worker_wt (
p_transaction_id NUMBER,
p_Org_Id NUMBER,
p_wip_txn_source_literal VARCHAR2,
p_wip_straight_time_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_denom_currency_code VARCHAR2,
p_user_id NUMBER,
p_login_id NUMBER,
p_req_id NUMBER,
p_prg_appl_id NUMBER,
p_prg_id NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2)
IS
/* The CURSOR has been changed owing to Bug#589460 and the UNION of WDJ and CFM
has been removed because we pick the prj/task reference from the WT Table
rather than from the Entity Definition.
*/
/* Added DISTINCT clause because ppf could have multiple records based on
effectivity dates for the same person_id Bug # 703956 */
/*Included the business_group_id parameter in the cursor bug 2124765 */
CURSOR sel_wt_trx_to_cost (C_transaction_id NUMBER,
C_organization_id NUMBER) IS
SELECT DISTINCT wta.transaction_date c_transaction_date,
ppf.business_group_id c_business_group_id,
nvl(ppf.employee_number,ppf.npw_number) c_employee_number,
wta.base_transaction_value c_base_transaction_value,
wt.primary_quantity c_primary_quantity,
wta.resource_id c_resource_id,
br.description c_resource_description,
br.expenditure_type c_expenditure_type,
wt.project_id c_project_id,
wt.task_id c_task_id,
bd.pa_expenditure_org_id c_pa_expenditure_org_id,
wt.acct_period_id c_acct_period_id,
wt.pm_cost_collector_group_id c_group_id,
wt.department_id c_department_id,
wt.transaction_type c_transaction_type,
wta.cost_element_id c_cost_element_id,
wt.wip_entity_id c_wip_entity_id,
wta.accounting_line_type c_accounting_line_type,
wt.primary_uom c_primary_uom,
wta.basis_resource_id c_basis_resource_id,
wta.reference_account c_reference_account,
wta.wip_sub_ledger_id c_wip_dr_sub_ledger_id,
nvl2(ppf.npw_number,'CTW',null) c_person_type
FROM wip_transaction_accounts wta,
wip_transactions wt,
bom_resources br,
bom_departments bd,
per_people_f ppf
WHERE wt.transaction_type in (1,2,3)
AND wta.accounting_line_type = 7
AND wt.transaction_id = wta.transaction_id
AND br.resource_id = wta.resource_id
AND bd.department_id = wt.department_id
AND bd.organization_id = wt.organization_id
AND wt.transaction_id = C_transaction_id
AND wt.organization_id = C_organization_id
AND ppf.person_id (+) = wt.employee_id
/* Bug:2395906*/
AND ppf.effective_start_date(+) <= trunc(sysdate)
AND ppf.effective_end_date(+) >= trunc(sysdate)
AND (ppf.employee_number is not null or ppf.person_id is null
OR ppf.npw_number is not null)
/*Added the above and condition to check if the employee_number
is not null.This is modified for porting bug #1573297 in 11.0
to 11.5 Bug 1660313*/
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Removed the join with PJM_PROJECT_PARAMETERS for bug 7328006
all the logic for the expenditure type will be handled by the
already existing API CST_eamCost_PUB.get_ExpType_for_DirectItem
which has been enhanced
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
UNION
SELECT DISTINCT wta.transaction_date c_transaction_date,
to_number(NULL) c_business_group_id,
NULL c_employee_number,
wta.base_transaction_value c_base_transaction_value,
wt.primary_quantity c_primary_quantity,
wta.resource_id c_resource_id,
pla.item_description c_resource_description,
NULL c_expenditure_type,
wt.project_id c_project_id,
wt.task_id c_task_id,
wt.organization_id c_pa_expenditure_org_id,
wt.acct_period_id c_acct_period_id,
wt.pm_cost_collector_group_id c_group_id,
wt.department_id c_department_id,
wt.transaction_type c_transaction_type,
wta.cost_element_id c_cost_element_id,
wt.wip_entity_id c_wip_entity_id,
wta.accounting_line_type c_accounting_line_type,
wt.primary_uom c_primary_uom,
wta.basis_resource_id c_basis_resource_id,
wta.reference_account c_reference_account,
wta.wip_sub_ledger_id c_wip_dr_sub_ledger_id,
NULL c_person_type
FROM wip_transaction_accounts wta,
wip_transactions wt,
pjm_project_parameters ppp,
po_lines_all pla
WHERE wt.transaction_type = 17
AND pla.po_line_id = wt.po_line_id
AND wta.accounting_line_type = 7
AND wt.transaction_id = wta.transaction_id
AND wt.transaction_id = C_transaction_id
AND wt.organization_id = C_organization_id;
SELECT decode(wt_rec.c_transaction_type,
17, 5, --Rcv Inspection(Direct Item)
decode(wt_rec.c_cost_element_id,
3, 4, --RES ABSO;RES
SELECT NVL(reference_account,-99)
INTO l_cr_code_combination_id
FROM wip_transaction_accounts
WHERE transaction_id = p_transaction_id
AND organization_id = p_org_id
AND cost_element_id = wt_rec.c_cost_element_id
AND accounting_line_type = l_accounting_line_type
AND NVL(resource_id,-99) = NVL(wt_rec.c_resource_id,-99);
SELECT MAX(WIP_SUB_LEDGER_ID)
INTO l_wip_cr_sub_ledger_id
FROM wip_transaction_accounts wta
WHERE transaction_id = p_transaction_id
AND reference_account = l_cr_code_combination_id
AND organization_id = p_org_id
AND cost_element_id = wt_rec.c_cost_element_id
AND accounting_line_type = l_accounting_line_type
AND NVL(resource_id,-99) = NVL(wt_rec.c_resource_id,-99);
for insertion into PA_TRANSACTION_INTERFACE.Bug Fix for
Bug 2124765 */
if wt_rec.c_business_group_id is not null THEN
Select haout.name into l_business_group_name
From hr_all_organization_units_tl haout
WHERE
haout.organization_id = wt_rec.c_business_group_id
AND haout.language = USERENV('LANG');
SELECT HAOUT.name
INTO l_business_group_name
FROM hr_all_organization_units_tl HAOUT
WHERE HAOUT.organization_id =
(SELECT COD.business_group_id
FROM cst_organization_definitions COD
WHERE COD.organization_id = p_org_id)
AND haout.language = USERENV('LANG');
UPDATE wip_transactions wt
SET wt.pm_cost_collected = NULL,
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.transaction_id = p_transaction_id;
NO_ROWS_TO_INSERT EXCEPTION;
CST_FAILED_INSERT_PTI EXCEPTION;
SELECT p_cap_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_source_project_number project_number,
l_source_task_number task_number,
decode(mcacd.cost_element_id,
2,0,
5,0,
(-1) * p_primary_quantity) quantity,
decode(mcacd.cost_element_id,
2,0,
5,0,
(-1)*sum(p_primary_quantity * mcacd.actual_cost))
raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.actual_cost)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_source_proj_org_id org_id,
(-1)*sum(p_primary_quantity *
mcacd.actual_cost) burdened_cost,
sum(mcacd.actual_cost) burdened_cost_rate,
decode( mcacd.cost_element_id,
2, p_bur_syslink_literal,
3, p_wip_syslink_literal,
4, p_wip_syslink_literal,
5, p_bur_syslink_literal,
p_inv_syslink_literal) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
mtl_material_transactions mmt
WHERE mmt.transaction_id = p_transaction_id
AND mmt.transaction_id = mcacd.transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
GROUP BY p_transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
br.EXPENDITURE_TYPE expenditure_type,
0 quantity, /* Qty=0 if l=burden */
0 raw_cost, /* RawCost=0 if l=burden */
br.description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
0 raw_cost_rate, /*RawCostrate=0 if l=burden */
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
macs.ACTUAL_COST*p_primary_quantity burdened_cost,
macs.ACTUAL_COST burdened_cost_rate,
p_bur_syslink_literal system_linkage,
'P' transaction_status_code,
br.resource_id resource_id,
macs.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_actual_cost_subelement macs,
bom_resources br,
mtl_parameters mp
WHERE macs.transaction_id = p_transaction_id
AND macs.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.cost_organization_id = br.organization_id
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND macs.RESOURCE_ID = br.RESOURCE_ID
AND macs.ACTUAL_COST <> 0;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_xfer_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_to_project_number project_number,
l_to_task_number task_number,
br.EXPENDITURE_TYPE expenditure_type,
0 quantity, /* Qty=0 if l=burden */
0 raw_cost, /* RawCost=0 if l=burden */
br.description expenditure_comment,
to_char(p_transfer_transaction_id) orig_transaction_reference,
0 raw_cost_rate, /*RawCostrate=0 if l=burden */
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_to_proj_org_id org_id,
(-1)*macs.ACTUAL_COST*p_primary_quantity burdened_cost,
macs.ACTUAL_COST burdened_cost_rate,
p_bur_syslink_literal system_linkage,
'P' transaction_status_code,
br.resource_id resource_id,
macs.cost_element_id cost_element_id,
l_xfer_currency_code denom_currency_code
FROM mtl_actual_cost_subelement macs,
bom_resources br
WHERE macs.transaction_id = p_transfer_transaction_id
AND macs.ORGANIZATION_ID = br.ORGANIZATION_ID
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND macs.RESOURCE_ID = br.RESOURCE_ID
AND macs.ACTUAL_COST <> 0;
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,p_primary_quantity
)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
sum(mcacd.ACTUAL_COST)*p_primary_quantity
)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)
)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
((sum(mcacd.ACTUAL_COST)*p_primary_quantity) - (NVL((sum(temp.actual_cost)*p_primary_quantity),0))) burdened_cost,
(sum(mcacd.ACTUAL_COST)-(NVL(sum(temp.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
(Select SUM(actual_cost) actual_cost,
transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
from mtl_actual_cost_subelement macs
where transaction_id = p_transaction_id
and organization_id = p_organization_id
group by transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
) temp,
cst_cg_item_costs_view ccicv --PJMSTD
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1,ccicv.layer_id) --PJMSTD
AND temp.transaction_id(+) =
mcacd.transaction_id
AND temp.organization_id(+) = mcacd.organization_id
AND temp.cost_element_id(+) = mcacd.cost_element_id
AND temp.level_type(+) = mcacd.level_type
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1, 1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id
UNION ALL /* BUG #2972878, 2580132 */
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
sum(mcacd.ACTUAL_COST)*p_primary_quantity
))raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)
)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
((sum(mcacd.ACTUAL_COST)*p_primary_quantity) - (NVL((sum(temp.actual_cost)*p_primary_quantity),0))) burdened_cost,
(sum(mcacd.ACTUAL_COST)-(NVL(sum(temp.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
(Select SUM(actual_cost) actual_cost,
transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
from mtl_actual_cost_subelement macs
where transaction_id = p_transaction_id
and organization_id = p_organization_id
group by transaction_id,
organization_id,
cost_element_id,
level_type,
layer_id
) temp,
mtl_system_items msi
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.inventory_item_id = msi.inventory_item_id
AND mcacd.organization_id = msi.organization_id
AND msi.costing_enabled_flag = 'N'
AND mcacd.actual_cost <> 0
/* AND NOT EXISTS (
SELECT null
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id =
mcacd.transaction_id
AND macs.organization_id =
mcacd.organization_id
AND macs.cost_element_id =
mcacd.cost_element_id
AND macs.level_type = mcacd.level_type
)*/
AND temp.transaction_id(+) = mcacd.transaction_id
AND temp.organization_id(+) = mcacd.organization_id
AND temp.cost_element_id(+) = mcacd.cost_element_id
AND temp.level_type(+) = mcacd.level_type
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
decode(p_transaction_action_id,
3, l_xfer_organization_name,
l_organization_name) organization_name,
p_transaction_date expenditure_item_date,
l_to_project_number project_number,
l_to_task_number task_number,
decode(sign(p_primary_quantity),1, cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN) expenditure_type,
decode(mcacd.cost_element_id,2,0, decode(mcacd.cost_element_id,5,0,
(-1) * p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
(-1) * sum(mcacd.ACTUAL_COST)*p_primary_quantity)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transfer_transaction_id)
orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST)))
raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_to_proj_org_id org_id,
(-1)*((sum(mcacd.ACTUAL_COST)*p_primary_quantity)-
(NVL(sum(macs.actual_cost)*p_primary_quantity,0)))
burdened_cost,
(sum(mcacd.ACTUAL_COST)-
(NVL(sum(macs.actual_cost),0))) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
decode(p_transaction_action_id,
3, l_xfer_currency_code,
p_denom_currency_code) denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cost_elem_exp_types cceet,
mtl_actual_cost_subelement macs
WHERE mcacd.transaction_id = decode(p_transaction_action_id,
3, p_transfer_transaction_id ,
p_transaction_id)
AND mcacd.organization_id = decode(p_transaction_action_id,
3, p_transfer_organization_id,
p_organization_id)
AND cceet.cost_element_id = mcacd.cost_element_id
AND mcacd.actual_cost <> 0
AND ((l_primary_cost_method_snd = 1)
OR
EXISTS
( SELECT 'X' from cst_quantity_layers cql
where mcacd.layer_id = cql.layer_id
AND cql.organization_id = mcacd.organization_id
AND cql.inventory_item_id = p_inventory_item_id
AND cql.cost_group_id = nvl(p_transfer_cost_group_id,1)
)
)
AND macs.transaction_id(+) = mcacd.transaction_id
AND macs.organization_id(+) = mcacd.organization_id
AND macs.cost_element_id(+) = mcacd.cost_element_id
AND macs.level_type(+) = mcacd.level_type
GROUP BY mcacd.cost_element_id,
cceet.expenditure_type_in,
cceet.expenditure_type_out;
SELECT decode(p_type_class,1,p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_source_project_number project_number,
l_source_task_number task_number,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,(-1) * p_primary_quantity)) quantity,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,
(-1) * sum(mcacd.ACTUAL_COST) * p_primary_quantity)) raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,2,0,
decode(mcacd.cost_element_id,5,0,sum(mcacd.ACTUAL_COST))) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_source_proj_org_id org_id,
(-1) * sum(mcacd.ACTUAL_COST)*p_primary_quantity burdened_cost,
sum(mcacd.ACTUAL_COST) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cg_item_costs_view ccicv
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.actual_cost <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1, ccicv.layer_id) --PJMSTD
AND NOT EXISTS (
SELECT null
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id =
mcacd.transaction_id
AND macs.organization_id =
mcacd.organization_id
AND macs.cost_element_id =
mcacd.cost_element_id
AND macs.level_type = mcacd.level_type )
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1,1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id;
SELECT p_inv_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
l_organization_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
decode(sign(p_primary_quantity),1, cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN) expenditure_type,
-- bug 923134
decode(mcacd.cost_element_id,
2,0,
5,0,p_primary_quantity) quantity,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.payback_variance_amount)* abs(p_primary_quantity))
raw_cost,
p_item_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(mcacd.cost_element_id,
2,0,
5,0,
sum(mcacd.payback_variance_amount)) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
sum(mcacd.payback_variance_amount)* abs(p_primary_quantity)
burdened_cost,
sum(mcacd.payback_variance_amount) burdened_cost_rate,
decode(mcacd.cost_element_id,2, p_bur_syslink_literal,
decode(mcacd.cost_element_id,3, p_wip_syslink_literal,
decode(mcacd.cost_element_id,4, p_wip_syslink_literal,
decode(mcacd.cost_element_id,5, p_bur_syslink_literal,
p_inv_syslink_literal)))) system_linkage,
'P' transaction_status_code ,
mcacd.cost_element_id cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_cst_actual_cost_details mcacd,
cst_cost_elem_exp_types cceet,
cst_cg_item_costs_view ccicv
WHERE mcacd.transaction_id = p_transaction_id
AND mcacd.organization_id = p_organization_id
AND mcacd.inventory_item_id = p_inventory_item_id
AND mcacd.payback_variance_amount <> 0
AND mcacd.layer_id = decode(p_primary_cost_method,
1, -1, ccicv.layer_id) --PJMSTD
AND cceet.cost_element_id = mcacd.cost_element_id
AND ccicv.organization_id = p_organization_id
AND ccicv.inventory_item_id = p_inventory_item_id
AND ccicv.cost_group_id = decode(p_primary_cost_method,
1,1, p_cost_group_id) --PJMSTD
GROUP BY mcacd.transaction_id,
mcacd.cost_element_id,
cceet.expenditure_type_in,
cceet.expenditure_type_out;
SELECT decode(p_type_class,1,
p_cap_txn_source_literal,
p_inv_txn_source_literal) transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
NULL employee_number,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_organization_name,
29, l_organization_name, l_xfer_organization_name),
l_organization_name) organization_name,
p_transaction_date expenditure_item_date,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_project_number,
29, l_project_number, l_to_project_number),
l_project_number) project_number,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, l_task_number,
29, l_task_number, l_to_task_number),
l_task_number) task_number,
nvl(ppp.ppv_expenditure_type, pop.ppv_expenditure_type) expenditure_type,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, p_primary_quantity,
29, p_primary_quantity, (-1)*p_primary_quantity),
p_primary_quantity) quantity,
mmt.variance_amount raw_cost,
p_item_description expenditure_comment,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, to_char(p_transaction_id),
29, to_char(p_transaction_id),
21, to_char(p_transaction_id),
to_char(p_transfer_transaction_id)),
to_char(p_transaction_id)) orig_transaction_reference,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, mmt.variance_amount/p_primary_quantity,
29, mmt.variance_amount/p_primary_quantity,
(-1)*mmt.variance_amount/p_primary_quantity),
mmt.variance_amount/p_primary_quantity) raw_cost_rate,
'Y' unmatched_negative_txn_flag,
-999999 dr_code_combination_id,
-999999 cr_code_combination_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_proj_org_id org_id,
mmt.variance_amount burdened_cost,
decode(sign(p_primary_quantity),-1,
decode(p_transaction_action_id,
1, mmt.variance_amount/p_primary_quantity,
29, mmt.variance_amount/p_primary_quantity,
(-1)* mmt.variance_amount/p_primary_quantity),
mmt.variance_amount/p_primary_quantity) burdened_cost_rate,
p_inv_syslink_literal system_linkage,
'P' transaction_status_code ,
NULL cost_element_id,
p_denom_currency_code denom_currency_code
FROM mtl_material_transactions mmt,
pjm_project_parameters ppp,
pjm_org_parameters pop
WHERE mmt.transaction_id = decode(ppv_txfr_flag,1, p_transfer_transaction_id,
p_transaction_id)
AND mmt.organization_id = decode(ppv_txfr_flag,1,p_transfer_organization_id,
p_organization_id)
AND NVL(mmt.variance_amount,0) <> 0
AND pop.organization_id = decode(sign(p_primary_quantity),-1,
decode( p_transaction_action_id,
1, p_organization_id,
29, p_organization_id,
p_transfer_organization_id),
p_organization_id)
AND ppp.organization_id (+) = pop.organization_id
AND ppp.project_id (+) = decode(sign(p_primary_quantity),-1,
decode( p_transaction_action_id,
1, p_project_id,
29, p_project_id, p_to_project_id),
p_project_id);
SELECT primary_cost_method, decode(pm_cost_collection_enabled, 1, 1, 0)
INTO l_primary_cost_method_snd, l_cost_collection_enabled_snd
FROM mtl_parameters
WHERE organization_id = p_transfer_organization_id ;
SELECT 'CC'|| substr( replace( lpad(
to_char(p_Group_Id,'9999999999999'),14,'0') ,' ','0'),-8)
INTO l_batch
FROM DUAL;
select org_information3
into l_operating_unit
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
SELECT max(pop.common_project_id)
INTO l_default_project
FROM pjm_org_parameters pop,
mtl_material_transactions mmt
WHERE pop.organization_id = mmt.organization_id
AND mmt.transaction_id = p_transaction_id;
SELECT hou.name
INTO l_organization_name
FROM hr_organization_units hou
WHERE hou.organization_id = p_exp_org_id;
SELECT haou.name
INTO l_recv_iss_organization_name
FROM hr_all_organization_units haou
WHERE haou.organization_id = p_organization_id;
SELECT hou.name
INTO l_xfer_organization_name
FROM hr_organization_units hou
WHERE hou.organization_id = p_transfer_organization_id;
SELECT currency_code
INTO l_xfer_currency_code
FROM cst_organization_definitions cod
WHERE cod.organization_id = p_transfer_organization_id;
SELECT ppa.org_id,
ppa.segment1 -- project number
INTO l_proj_org_id,
l_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_project_id;
SELECT segment1 -- project number
INTO l_project_number
FROM pa_projects_all
WHERE project_id = p_project_id; */
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT ppa.org_id,
ppa.segment1 -- project number
INTO l_to_proj_org_id,
l_to_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_to_project_id;
SELECT segment1 -- project number
INTO l_to_project_number
FROM pa_projects_all
WHERE project_id = p_to_project_id; */
SELECT task_number
INTO l_to_task_number
FROM pa_tasks
WHERE project_id = p_to_project_id
AND task_id = p_to_task_id;
SELECT ppa.org_id,
segment1
INTO l_source_proj_org_id,
l_source_project_number
FROM pa_projects_all ppa
WHERE ppa.project_id = p_source_project_id;
SELECT segment1 -- project number
INTO l_source_project_number
FROM pa_projects_all
WHERE project_id = p_source_project_id; */
SELECT task_number
INTO l_source_task_number
FROM pa_tasks
WHERE project_id = p_source_project_id
AND task_id = p_source_task_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV, mtl_material_transactions MMT
WHERE MSNV.from_organization_id = p_organization_id
AND MSNV.to_organization_id = p_transfer_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV, mtl_material_transactions MMT
WHERE MSNV.from_organization_id = p_transfer_organization_id
AND MSNV.to_organization_id = p_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT schedule_close_date
INTO l_gl_date
FROM org_acct_periods oac
WHERE oac.organization_id = p_organization_id
AND oac.acct_period_id = p_acct_period_id;*/
SELECT NVL(MAX(macs.transaction_id),-99)
INTO l_earn_moh
FROM mtl_actual_cost_subelement macs
WHERE macs.transaction_id = p_transaction_id
AND macs.organization_id = p_organization_id
AND macs.actual_cost <> 0
AND macs.level_type = 1
AND macs.cost_element_id = 2;
anything to insert */
/* bug 4655264. Need to make sure that p_to_project_id is not nULL before transferring */
l_stmt_num := 106;
select NVL(MAX(macs.transaction_id),-99)
into l_earn_tomoh
from mtl_actual_cost_subelement macs
WHERE macs.transaction_id = p_transfer_transaction_id
AND macs.organization_id = p_transfer_organization_id
AND macs.actual_cost <> 0
AND macs.level_type = 1
AND macs.cost_element_id = 2;
SELECT transaction_type_id
INTO l_txn_type
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT
DECODE(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec1.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec1.transaction_source,
p_batch_name =>c_rec1.batch_name,
p_expenditure_ending_date =>
c_rec1.expenditure_ending_date,
p_employee_number =>c_rec1.employee_number,
p_organization_name =>
c_rec1.organization_name,
p_expenditure_item_date =>
c_rec1.expenditure_item_date,
p_project_number =>c_rec1.project_number,
p_task_number =>c_rec1.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec1.quantity,
p_raw_cost =>c_rec1.raw_cost,
p_expenditure_comment =>
c_rec1.expenditure_comment,
p_orig_transaction_reference =>
c_rec1.orig_transaction_reference,
p_raw_cost_rate =>c_rec1.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec1.unmatched_negative_txn_flag,
p_gl_date =>c_rec1.gl_date,
p_org_id =>c_rec1.org_id,
p_burdened_cost =>c_rec1.burdened_cost,
p_burdened_cost_rate =>
c_rec1.burdened_cost_rate,
p_system_linkage =>c_rec1.system_linkage,
p_transaction_status_code =>
c_rec1.transaction_status_code,
p_denom_currency_code =>
c_rec1.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec1.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec2.transaction_source,
p_batch_name =>c_rec2.batch_name,
p_expenditure_ending_date =>
c_rec2.expenditure_ending_date,
p_employee_number =>c_rec2.employee_number,
p_organization_name =>
c_rec2.organization_name,
p_expenditure_item_date =>
c_rec2.expenditure_item_date,
p_project_number =>c_rec2.project_number,
p_task_number =>c_rec2.task_number,
p_expenditure_type =>c_rec2.expenditure_type,
p_pa_quantity =>c_rec2.quantity,
p_raw_cost =>c_rec2.raw_cost,
p_expenditure_comment =>
c_rec2.expenditure_comment,
p_orig_transaction_reference =>
c_rec2.orig_transaction_reference,
p_raw_cost_rate =>c_rec2.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec2.unmatched_negative_txn_flag,
p_gl_date =>c_rec2.gl_date,
p_org_id =>c_rec2.org_id,
p_burdened_cost =>c_rec2.burdened_cost,
p_burdened_cost_rate =>
c_rec2.burdened_cost_rate,
p_system_linkage =>c_rec2.system_linkage,
p_transaction_status_code =>
c_rec2.transaction_status_code,
p_denom_currency_code =>
c_rec2.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec2.cost_element_id,
p_resource_id =>c_rec2.resource_id,
p_source_flag =>-1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
/* Insert Material Overhead absorption for the receiving side in a direct
interorg transaction */
If (l_earn_tomoh <> -99 AND p_transaction_id <> -99 ) then
FND_FILE.PUT_LINE(FND_FILE.LOG,'Collecting the MOH absorption on the receiving side');
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec9.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_transaction_source,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
/* Now call insert routine to insert into interface */
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec9.batch_name,
p_expenditure_ending_date =>
c_rec9.expenditure_ending_date,
p_employee_number =>c_rec9.employee_number,
p_organization_name =>
c_rec9.organization_name,
p_expenditure_item_date =>
c_rec9.expenditure_item_date,
p_project_number =>c_rec9.project_number,
p_task_number =>c_rec9.task_number,
p_expenditure_type =>c_rec9.expenditure_type,
p_pa_quantity =>c_rec9.quantity,
p_raw_cost =>c_rec9.raw_cost,
p_expenditure_comment =>
c_rec9.expenditure_comment,
p_orig_transaction_reference =>
c_rec9.orig_transaction_reference,
p_raw_cost_rate =>c_rec9.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec9.unmatched_negative_txn_flag,
p_gl_date =>c_rec9.gl_date,
p_org_id =>c_rec9.org_id,
p_burdened_cost =>c_rec9.burdened_cost,
p_burdened_cost_rate =>
c_rec9.burdened_cost_rate,
p_system_linkage =>c_rec9.system_linkage,
p_transaction_status_code =>
c_rec9.transaction_status_code,
p_denom_currency_code =>
c_rec9.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec9.cost_element_id,
p_resource_id =>c_rec9.resource_id,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
SELECT
EXPENDITURE_TYPE
INTO
l_exp_type
FROM
MTL_MATERIAL_TRANSACTIONS
WHERE
transaction_id = p_transaction_id;
SELECT
decode(c_rec3.cost_element_id,1,
decode(p_transaction_source_type_id,1,
p_first_matl_se_exp_type,
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_IN,
cceet.EXPENDITURE_TYPE_OUT)),
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_IN,
cceet.EXPENDITURE_TYPE_OUT))
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec3.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec3.transaction_source,
p_batch_name =>c_rec3.batch_name,
p_expenditure_ending_date =>
c_rec3.expenditure_ending_date,
p_employee_number =>c_rec3.employee_number,
p_organization_name =>
NVL(l_recv_iss_organization_name,
c_rec3.organization_name),
p_expenditure_item_date =>
c_rec3.expenditure_item_date,
p_project_number =>c_rec3.project_number,
p_task_number =>c_rec3.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec3.quantity,
p_raw_cost =>c_rec3.raw_cost,
p_expenditure_comment =>
c_rec3.expenditure_comment,
p_orig_transaction_reference =>
c_rec3.orig_transaction_reference,
p_raw_cost_rate =>c_rec3.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec3.unmatched_negative_txn_flag,
p_gl_date =>c_rec3.gl_date,
p_org_id =>c_rec3.org_id,
p_burdened_cost =>c_rec3.burdened_cost,
p_burdened_cost_rate =>
c_rec3.burdened_cost_rate,
p_system_linkage =>c_rec3.system_linkage,
p_transaction_status_code =>
c_rec3.transaction_status_code,
p_denom_currency_code =>
c_rec3.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec3.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
/* bug 3978501 commenting out the excpetion raise as there maybe PPV to insert */
/* RAISE NO_ROWS_TO_INSERT; */
SELECT EXPENDITURE_TYPE
INTO c_rec4.EXPENDITURE_TYPE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = p_transaction_id;
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec4.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_transaction_source,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec4.batch_name,
p_expenditure_ending_date =>
c_rec4.expenditure_ending_date,
p_employee_number =>c_rec4.employee_number,
p_organization_name =>
c_rec4.organization_name,
p_expenditure_item_date =>
c_rec4.expenditure_item_date,
p_project_number =>c_rec4.project_number,
p_task_number =>c_rec4.task_number,
p_expenditure_type =>c_rec4.expenditure_type,
p_pa_quantity =>c_rec4.quantity,
p_raw_cost =>c_rec4.raw_cost,
p_expenditure_comment =>
c_rec4.expenditure_comment,
p_orig_transaction_reference =>
c_rec4.orig_transaction_reference,
p_raw_cost_rate =>c_rec4.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec4.unmatched_negative_txn_flag,
p_gl_date =>c_rec4.gl_date,
p_org_id =>c_rec4.org_id,
p_burdened_cost =>c_rec4.burdened_cost,
p_burdened_cost_rate =>
c_rec4.burdened_cost_rate,
p_system_linkage =>c_rec4.system_linkage,
p_transaction_status_code =>
c_rec4.transaction_status_code,
p_denom_currency_code =>
c_rec4.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec4.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
If the receiving txn has not been costed yet, update MMT with a
warning but do not reset the pm_cost_collected flag to error.
*/
SELECT mmt.costed_flag
INTO l_costed_flag
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = decode(p_transaction_action_id,
3, p_transfer_transaction_id ,
p_transaction_id)
AND mmt.organization_id = decode(p_transaction_action_id,
3, p_transfer_organization_id,
p_organization_id);
/* bug 3978501 commenting out the excpetion part as there may be PPV to insert */
/* RAISE NO_ROWS_TO_INSERT; */
SELECT
decode(sign(p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec5.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec5.transaction_source,
p_batch_name =>c_rec5.batch_name,
p_expenditure_ending_date =>
c_rec5.expenditure_ending_date,
p_employee_number =>c_rec5.employee_number,
p_organization_name =>
c_rec5.organization_name,
p_expenditure_item_date =>
c_rec5.expenditure_item_date,
p_project_number =>c_rec5.project_number,
p_task_number =>c_rec5.task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>c_rec5.quantity,
p_raw_cost =>c_rec5.raw_cost,
p_expenditure_comment =>
c_rec5.expenditure_comment,
p_orig_transaction_reference =>
c_rec5.orig_transaction_reference,
p_raw_cost_rate =>c_rec5.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec5.unmatched_negative_txn_flag,
p_gl_date =>c_rec5.gl_date,
p_org_id =>c_rec5.org_id,
p_burdened_cost =>c_rec5.burdened_cost,
p_burdened_cost_rate =>
c_rec5.burdened_cost_rate,
p_system_linkage =>c_rec5.system_linkage,
p_transaction_status_code =>
c_rec5.transaction_status_code,
p_denom_currency_code =>
c_rec5.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec5.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>1,
p_variance_flag =>-1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec6.transaction_source,
p_batch_name =>c_rec6.batch_name,
p_expenditure_ending_date =>
c_rec6.expenditure_ending_date,
p_employee_number =>c_rec6.employee_number,
p_organization_name =>
c_rec6.organization_name,
p_expenditure_item_date =>
c_rec6.expenditure_item_date,
p_project_number =>c_rec6.project_number,
p_task_number =>c_rec6.task_number,
p_expenditure_type =>c_rec6.expenditure_type,
p_pa_quantity =>c_rec6.quantity,
p_raw_cost =>c_rec6.raw_cost,
p_expenditure_comment =>
c_rec6.expenditure_comment,
p_orig_transaction_reference =>
c_rec6.orig_transaction_reference,
p_raw_cost_rate =>c_rec6.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec6.unmatched_negative_txn_flag,
p_gl_date =>c_rec6.gl_date,
p_org_id =>c_rec6.org_id,
p_burdened_cost =>c_rec6.burdened_cost,
p_burdened_cost_rate =>
c_rec6.burdened_cost_rate,
p_system_linkage =>c_rec6.system_linkage,
p_transaction_status_code =>
c_rec6.transaction_status_code,
p_denom_currency_code =>
c_rec6.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec6.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
SELECT
decode(sign(-1 * p_primary_quantity),1,
cceet.EXPENDITURE_TYPE_OUT,
cceet.EXPENDITURE_TYPE_IN)
INTO l_exp_type
FROM cst_cost_elem_exp_types cceet
WHERE cceet.cost_element_id = c_rec6.cost_element_id;
pm_insert_pti_pvt
(p_transaction_source =>
c_rec6.transaction_source,
p_batch_name =>c_rec6.batch_name,
p_expenditure_ending_date =>
c_rec6.expenditure_ending_date,
p_employee_number =>c_rec6.employee_number,
p_organization_name =>
c_rec6.organization_name,
p_expenditure_item_date =>
c_rec6.expenditure_item_date,
p_project_number =>l_to_project_number,
p_task_number =>l_to_task_number,
p_expenditure_type =>l_exp_type,
p_pa_quantity =>-1 * c_rec6.quantity,
p_raw_cost =>-1 * c_rec6.raw_cost,
p_expenditure_comment =>
c_rec6.expenditure_comment,
p_orig_transaction_reference =>to_char(p_transfer_transaction_id),
p_raw_cost_rate =>-1 * c_rec6.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec6.unmatched_negative_txn_flag,
p_gl_date =>c_rec6.gl_date,
p_org_id =>l_to_proj_org_id,
p_burdened_cost =>-1 * c_rec6.burdened_cost,
p_burdened_cost_rate =>
-1 * c_rec6.burdened_cost_rate,
p_system_linkage =>c_rec6.system_linkage,
p_transaction_status_code =>
c_rec6.transaction_status_code,
p_denom_currency_code =>
c_rec6.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec6.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>-1 * p_primary_quantity ,
p_transfer_organization_id =>
p_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_to_project_id,
p_task_id =>p_to_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_transfer_cost_group_id,
p_transfer_cost_group_id =>
p_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_project_id,
p_to_task_id =>p_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
p_inv_txn_source_literal,
p_cap_txn_source_literal =>
p_cap_txn_source_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
RAISE NO_ROWS_TO_INSERT;
select organization_id into l_org_id
from mtl_material_transactions
where transaction_id = c_rec7.orig_transaction_reference;
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_org_id ;
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_NO_ACCOUNTS';
Select pts1.transaction_source,
pts1.transaction_source,
pts1.transaction_source
into l_transaction_source,
l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
From pa_transaction_sources pts1
Where pts1.transaction_source = 'PJM_CSTBP_INV_ACCOUNTS';
SELECT pts1.transaction_source,
pts2.transaction_source
INTO l_inv_txn_src_literal,
l_cap_inv_txn_src_literal
FROM pa_transaction_sources pts1,
pa_transaction_sources pts2
WHERE pts1.transaction_source = 'Inventory'
AND pts2.transaction_source = 'Inventory Misc';
SELECT decode(p_type_class,1,
l_cap_inv_txn_src_literal,
l_inv_txn_src_literal)
INTO l_transaction_source
from dual;
pm_insert_pti_pvt
(p_transaction_source =>
l_transaction_source,
p_batch_name =>c_rec7.batch_name,
p_expenditure_ending_date =>
c_rec7.expenditure_ending_date,
p_employee_number =>c_rec7.employee_number,
p_organization_name =>
c_rec7.organization_name,
p_expenditure_item_date =>
c_rec7.expenditure_item_date,
p_project_number =>c_rec7.project_number,
p_task_number =>c_rec7.task_number,
p_expenditure_type =>c_rec7.expenditure_type,
p_pa_quantity =>c_rec7.quantity,
p_raw_cost =>c_rec7.raw_cost,
p_expenditure_comment =>
c_rec7.expenditure_comment,
p_orig_transaction_reference =>
c_rec7.orig_transaction_reference,
p_raw_cost_rate =>c_rec7.raw_cost_rate,
p_unmatched_negative_txn_flag=>
c_rec7.unmatched_negative_txn_flag,
p_gl_date =>c_rec7.gl_date,
p_org_id =>c_rec7.org_id,
p_burdened_cost =>c_rec7.burdened_cost,
p_burdened_cost_rate =>
c_rec7.burdened_cost_rate,
p_system_linkage =>c_rec7.system_linkage,
p_transaction_status_code =>
c_rec7.transaction_status_code,
p_denom_currency_code =>
c_rec7.denom_currency_code,
p_transaction_id =>p_transaction_id,
p_transaction_action_id =>p_transaction_action_id,
p_transaction_source_type_id =>
p_transaction_source_type_id,
p_organization_id =>p_organization_id,
p_inventory_item_id =>p_inventory_item_id,
p_cost_element_id =>c_rec7.cost_element_id,
p_resource_id =>NULL,
p_source_flag =>-1,
p_variance_flag =>1,
p_primary_quantity =>p_primary_quantity ,
p_transfer_organization_id =>
p_transfer_organization_id,
p_fob_point =>l_fob_point,
p_wip_entity_id =>NULL,
p_basis_resource =>NULL,
p_type_class =>p_type_class,
p_project_id =>p_project_id,
p_task_id =>p_task_id,
p_transaction_date =>p_transaction_date,
p_cost_group_id =>p_cost_group_id,
p_transfer_cost_group_id =>
p_transfer_cost_group_id,
p_transaction_source_id =>
p_transaction_source_id,
p_to_project_id =>p_to_project_id,
p_to_task_id =>p_to_task_id,
p_source_project_id =>p_source_project_id,
p_source_task_id =>p_source_task_id,
p_transfer_transaction_id =>
p_transfer_transaction_id,
p_primary_cost_method =>p_primary_cost_method,
p_acct_period_id =>p_acct_period_id,
p_exp_org_id =>p_exp_org_id,
p_distribution_account_id =>
p_distribution_account_id,
p_proj_job_ind =>p_proj_job_ind,
p_first_matl_se_exp_type =>
p_first_matl_se_exp_type,
p_inv_txn_source_literal =>
l_inv_txn_src_literal,
p_cap_txn_source_literal =>
l_cap_inv_txn_src_literal,
p_inv_syslink_literal =>p_inv_syslink_literal,
p_bur_syslink_literal =>p_bur_syslink_literal,
p_wip_syslink_literal =>p_wip_syslink_literal,
p_user_def_exp_type =>p_user_def_exp_type,
p_flow_schedule =>p_flow_schedule,
p_si_asset_yes_no =>p_si_asset_yes_no,
p_transfer_si_asset_yes_no =>
p_transfer_si_asset_yes_no,
O_err_num =>l_err_num,
O_err_code =>l_err_code,
O_err_msg =>l_err_msg
);
RAISE CST_FAILED_INSERT_PTI;
/* bug 3978501. check to see if r have been inserted and update l_no_ppv */
if l_rownum <> 0 then
l_no_ppv := 0;
RAISE NO_ROWS_TO_INSERT;
WHEN NO_ROWS_TO_INSERT THEN
O_err_num := 20002;
WHEN CST_FAILED_INSERT_PTI THEN
O_err_num := 20005;
SELECT decode(br.expenditure_type,NULL,
decode(br1.expenditure_type,NULL,'NO VALUE',
br1.expenditure_type),
br.expenditure_type)
INTO l_exp_type
FROM mtl_parameters mp,
cst_item_cost_details cicd,
bom_resources br,
bom_resources br1
WHERE mp.organization_id = p_organization_id
AND mp.cost_organization_id = cicd.organization_id (+)
AND cicd.inventory_item_id (+) = p_inventory_item_id
AND cicd.cost_type_id (+) = decode(p_cost_method, 1, 1,
p_avg_rates_cost_type_id)
AND cicd.cost_element_id (+) = 1
AND cicd.organization_id = br.organization_id (+)
AND cicd.resource_id = br.resource_id (+)
AND mp.cost_organization_id = br1.organization_id (+)
AND mp.default_material_cost_id = br1.resource_id (+)
AND rownum=1;
SELECT 'NO VALUE'
INTO l_exp_type
FROM mtl_actual_cost_subelement macs,
bom_resources br
WHERE macs.transaction_id = p_transaction_id
AND macs.organization_id = p_organization_id
AND macs.cost_element_id = 2
AND macs.level_type = 1
AND br.RESOURCE_ID = macs.RESOURCE_ID
AND br.ORGANIZATION_ID = macs.ORGANIZATION_ID
AND br.expenditure_type IS NULL;
/* update the errorcode and error_explanation fields inspite of the
maintenance of the log file */
UPDATE mtl_material_transactions mmt
SET mmt.pm_cost_collected = 'E',
mmt.error_code = p_error_code,
mmt.error_explanation = p_error_explanation,
mmt.last_update_date = sysdate,
mmt.last_updated_by = p_user_id,
mmt.last_update_login = p_login_id,
mmt.request_id = p_req_id,
mmt.program_application_id = p_prg_appl_id,
mmt.program_id = p_prg_id,
mmt.program_update_date = sysdate
WHERE mmt.transaction_id = p_transaction_id ;
SELECT p_wip_txn_source_literal transaction_source,
l_batch batch_name,
l_exp_end_date expenditure_ending_date,
p_employee_number employee_number,
p_exp_org_name organization_name,
p_transaction_date expenditure_item_date,
l_project_number project_number,
l_task_number task_number,
p_expenditure_type expenditure_type,
p_denom_currency_code denom_currency_code,
decode(p_cost_element_id,
5,0,
decode(p_primary_quantity,
NULL,p_base_transaction_value,
p_primary_quantity)) quantity,
decode(p_cost_element_id,5,0, p_base_transaction_value)
raw_cost,
p_resource_description expenditure_comment,
to_char(p_transaction_id) orig_transaction_reference,
decode(p_cost_element_id,
5,0,
decode(p_primary_quantity,
NULL, 1,
0, p_base_transaction_value,
p_base_transaction_value / p_primary_quantity))
raw_cost_rate,
'Y' unmatched_negative_txn_flag,
p_reference_account dr_code_combination_id,
p_cr_account cr_code_combination_id,
p_wip_dr_sub_ledger_id wip_dr_sub_ledger_id,
p_wip_cr_sub_ledger_id wip_cr_sub_ledger_id,
NULL cdl_system_reference1,
NULL cdl_system_reference2,
NULL cdl_system_reference3,
/*l_gl_date*/ /* Commented for bug 6266553 */ p_transaction_date gl_date,
l_multi_org_id org_id,
p_base_transaction_value burdened_cost,
decode(p_base_transaction_value,0,0,
decode(p_primary_quantity,
NULL,1,
0,p_base_transaction_value,
p_base_transaction_value/p_primary_quantity))
burdened_cost_rate,
decode(p_cost_element_id,5,
p_bur_syslink_literal, p_wip_syslink_literal)
system_linkage,
'P' transaction_status_code,
p_person_type person_type
FROM dual
WHERE p_base_transaction_value <> 0;
SELECT 'CC'|| substr( replace( lpad(
to_char(p_Group_Id,'9999999999999'),14,'0') ,' ','0'),-8)
INTO l_batch
FROM DUAL;
select org_information3
into l_operating_unit
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
SELECT schedule_close_date
INTO l_gl_date
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND oap.acct_period_id = p_acct_period_id;*/
SELECT segment1 -- project number
INTO l_project_number
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT task_number
INTO l_task_number
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
select to_number(org_information3)
into l_multi_org_id
from hr_organization_information
where organization_id = p_organization_id
and org_information_context ='Accounting Information';
Select NVL(system_linkage_function,c_rec.system_linkage) into l_syslinkage
from pa_transaction_sources
where transaction_source = l_transaction_source;
/* This following insert statement into pa_transaction_interface will be changes to insert into pa_transaction_interface_all */
/* modify the insert statement to insert the wip_resource_id and primary UOM code bug 3298023 */
/* get the primary UOM code directly from the transaction */
select primary_uom into l_uom_code
from wip_transactions
where transaction_id = p_transaction_id ;
select COALESCE(wta.currency_conversion_rate, wta.base_transaction_value/wta.transaction_value),
wta.currency_conversion_type,
wta.currency_conversion_date,
wta.currency_code
into l_cc_rate,
l_cc_type,
l_cc_date,
l_cc_foreign_currency
from wip_transaction_accounts wta
where wta.transaction_id = p_transaction_id
and wta.transaction_value is not null
and accounting_line_type <> 15 /*Changes for encumbrance SF project */
and rownum<2;
Select User_Conversion_Type
Into l_User_cc_Type
from gl_daily_conversion_types
where conversion_type = l_cc_type;
/* This following insert statement into pa_transaction_interface will be changes to insert into pa_transaction_interface_all */
INSERT INTO pa_transaction_interface_all
( transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
denom_raw_cost,
acct_raw_cost,
expenditure_comment,
orig_transaction_reference,
raw_cost_rate,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
cdl_system_reference1,
cdl_system_reference2,
cdl_system_reference3,
gl_date,
org_id,
denom_burdened_cost,
acct_burdened_cost,
burdened_cost_rate,
system_linkage,
transaction_status_code,
denom_currency_code,
acct_exchange_rate,
acct_rate_type,
acct_rate_date,
person_business_group_name,
wip_resource_id,
unit_of_measure,
cdl_system_reference4, --WIP cr. sub Ledger ID
cdl_system_reference5, --WIP Dr. sub LEDGER ID
person_type
)
VALUES
( l_transaction_source,
c_rec.batch_name,
c_rec.expenditure_ending_date,
c_rec.employee_number,
c_rec.organization_name,
c_rec.expenditure_item_date,
c_rec.project_number,
c_rec.task_number,
c_rec.expenditure_type,
c_rec.quantity,
decode(l_cc_rate, null, null, (l_raw_cost/l_cc_rate)), /*Foreign currency value*/
l_raw_cost, /*Functional currency value */
c_rec.expenditure_comment,
c_rec.orig_transaction_reference,
l_raw_cost_rate,
c_rec.unmatched_negative_txn_flag,
l_dr_code_combination_id,
l_cr_code_combination_id,
c_rec.cdl_system_reference1,
c_rec.cdl_system_reference2,
c_rec.cdl_system_reference3,
c_rec.gl_date,
c_rec.org_id,
decode(l_cc_rate, null, null, (l_burdened_cost/l_cc_rate)), /*Foreign currency value*/
l_burdened_cost, /*Functional currency value*/
l_burdened_cost_rate,
l_syslinkage,
c_rec.transaction_status_code,
l_cc_foreign_currency,
l_cc_rate,
l_user_cc_type,
l_cc_date,
p_business_group_name,
p_resource_id,
l_uom_code,
l_wip_cr_sub_ledger_id,
l_wip_dr_sub_ledger_id,
c_rec.person_type
);
SELECT hou.name
INTO l_organization_name
FROM bom_departments bd,
hr_organization_units hou
WHERE hou.organization_id =
bd.pa_expenditure_org_id
AND bd.organization_id = p_organization_id
AND bd.department_id = p_department_id;
UPDATE wip_transactions wt
SET wt.pm_cost_collected = 'E',
wt.last_update_date = sysdate,
wt.last_updated_by = p_user_id,
wt.last_update_login = p_login_id,
wt.request_id = p_req_id,
wt.program_application_id = p_prg_appl_id,
wt.program_id = p_prg_id,
wt.program_update_date = sysdate
WHERE wt.transaction_id = p_transaction_id ;
UPDATE wip_txn_interface_errors wtie
SET wtie.error_message = p_error_explanation,
wtie.last_update_date = sysdate,
wtie.last_updated_by = p_user_id,
wtie.last_update_login = p_login_id,
wtie.request_id = p_req_id,
wtie.program_application_id = p_prg_appl_id,
wtie.program_id = p_prg_id,
wtie.program_update_date = sysdate
WHERE wtie.error_column='PM_COST_COLLECTED'
AND wtie.transaction_id = p_transaction_id;
INSERT INTO wip_txn_interface_errors
( transaction_id,
error_message,
error_column,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES ( p_transaction_id,
p_error_explanation,
'PM_COST_COLLECTED',
sysdate,
p_user_id,
sysdate,
p_user_id,
p_login_id,
p_req_id,
p_prg_appl_id,
p_prg_id,
sysdate
);
SELECT
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.organization_id,
mmt.transfer_organization_id,
mmt.transfer_transaction_id,
mmt.cost_group_id,
mmt.transfer_cost_group_id,
mmt.transfer_subinventory,
mmt.inventory_item_id,
mmt.primary_quantity,
mmt.transaction_type_id,
mtt.type_class
INTO l_transaction_action_id,
l_transaction_source_type_id,
l_organization_id,
l_xfer_organization_id,
l_xfer_transaction_id,
l_cg_id,
l_xfer_cg_id,
l_transfer_subinventory,
l_inventory_item_id,
l_mmt_primary_quantity,
l_txn_type_id,
l_type_class
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_id = p_transaction_id
AND mtt.transaction_type_id = mmt.transaction_type_id;
SELECT mmt.subinventory_code,
msi.asset_inventory
INTO
l_subinventory_code,
l_si_asset_yes_no
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_SECONDARY_INVENTORIES msi
WHERE
mmt.transaction_id = p_transaction_id
AND msi.secondary_inventory_name = mmt.subinventory_code
AND msi.organization_id = mmt.organization_id;
SELECT decode(inventory_asset_flag, 'Y',0,1)
INTO l_exp_item
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id = l_organization_id;
SELECT decode(l_exp_item,1,1,decode(l_si_asset_yes_no,1,0,1))
INTO l_exp_flag
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = l_subinventory_code
AND msi.organization_id = l_organization_id;
SELECT msub.asset_inventory,
decode(mitems.inventory_asset_flag,
'Y', decode(msub.asset_inventory,1,0,1), 1)
INTO l_transfer_si_asset_yes_no,
l_xfer_exp_flag
FROM mtl_secondary_inventories msub,
mtl_system_items mitems
WHERE msub.secondary_inventory_name = l_transfer_subinventory
AND msub.organization_id = l_xfer_organization_id
AND mitems.inventory_item_id = l_inventory_item_id
AND mitems.organization_id = l_xfer_organization_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point) INTO l_fob_point
FROM mtl_shipping_network_view MSNV,
mtl_material_transactions MMT
WHERE MSNV.from_organization_id = l_organization_id
AND MSNV.to_organization_id = l_xfer_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT nvl(MMT.fob_point, MSNV.fob_point)
INTO l_fob_point
FROM mtl_shipping_network_view MSNV,
mtl_material_transactions MMT
WHERE MSNV.from_organization_id = l_xfer_organization_id
AND MSNV.to_organization_id = l_organization_id
AND MMT.transaction_id = p_transaction_id;
SELECT primary_cost_method
INTO l_cost_method
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT primary_cost_method
INTO l_xfer_cost_method
FROM mtl_parameters
WHERE organization_id = l_xfer_organization_id;
SELECT NVL(MAX(mta.reference_account), -999999)
INTO O_dr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE
mta.transaction_id = l_mta_transaction_id AND
mta.organization_id = l_mta_organization_id AND
mta.inventory_item_id = l_inventory_item_id AND
nvl(mta.cost_element_id, -99) = l_cost_element_id AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = decode(p_variance_flag, 1, mta.primary_quantity,
decode(l_source_flag, 1,
decode(l_transaction_action_id, 3, mta.primary_quantity,
l_mta_primary_quantity), l_mta_primary_quantity)) AND
(((l_citw_flag = 1) AND
(mta.transaction_source_type_id = l_wip_txn_source_type)) OR(l_citw_flag <> 1));
SELECT MAX(INV_SUB_LEDGER_ID)
INTO O_inv_dr_sub_ledger_id
FROM mtl_transaction_accounts mta
WHERE
mta.reference_account = O_dr_code_combination_id AND
mta.transaction_id = l_mta_transaction_id AND
mta.organization_id = l_mta_organization_id AND
mta.inventory_item_id = l_inventory_item_id AND
nvl(mta.cost_element_id, -99) = l_cost_element_id AND
mta.accounting_line_type = l_accounting_line_type AND
mta.primary_quantity = decode(p_variance_flag, 1, mta.primary_quantity,
decode(l_source_flag, 1,
decode(l_transaction_action_id, 3, mta.primary_quantity,
l_mta_primary_quantity), l_mta_primary_quantity)) AND
(((l_citw_flag = 1) AND
(mta.transaction_source_type_id = l_wip_txn_source_type)) OR(l_citw_flag <> 1));
/* to pass it to pm_insert_pti_pvt and use it to populate */
/* the correct columns in projects interface for the */
/* functional and foreign currency, since the variables */
/* l_mta_transaction_id and l_mta_organization_id already */
/* contain the logic to select the appropiate transaction */
/* to query in MTA including the cases for subinventory */
/* transfers and direct interorg between the different */
/* combinations, we will query the subledger (MTA) to get */
/* the currency data */
/* In MTA only if transaction_value is populated, then we */
/* can assume that the transaction is a foreign currency */
/* transaction and only in those cases we should pass the */
/* currency information back */
l_stmt_num := 52;
select COALESCE(mta.currency_conversion_rate, mta.base_transaction_value/mta.transaction_value),
mta.currency_conversion_type,
mta.currency_conversion_date,
mta.currency_code
into l_cc_rate,
l_cc_type,
l_cc_date,
l_cc_foreign_currency
from mtl_transaction_accounts mta
where mta.transaction_id = l_mta_transaction_id
and mta.organization_id = l_mta_organization_id
and mta.accounting_line_type <> 15 /*Added for Encumbrance project */
and mta.transaction_value is not null
and rownum<2;
select currency_code
into l_cc_functional_currency
from cst_organization_definitions
where organization_id = l_mta_organization_id;
select user_conversion_type
into l_user_cc_type
from gl_daily_conversion_types
where conversion_type = l_cc_type;
SELECT
NVL((MAX(mta.reference_account)),-999999)
INTO O_cr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE mta.transaction_id = p_transaction_id
AND mta.organization_id = l_organization_id
AND mta.inventory_item_id = l_inventory_item_id
AND NVL(mta.cost_element_id,-99)= NVL(p_cost_element_id,-99)
AND mta.accounting_line_type = 1
AND mta.primary_quantity = decode(l_source_flag,1,l_mmt_primary_quantity,((-1)*l_mmt_primary_quantity))
AND mta.transaction_source_type_id = 13; --ALWAYS INV
Select MAX(INV_SUB_LEDGER_ID)
into O_inv_cr_sub_ledger_id
from mtl_transaction_accounts mta
where mta.reference_account = O_cr_code_combination_id
AND mta.transaction_id = p_transaction_id
AND mta.organization_id = l_organization_id
AND mta.inventory_item_id = l_inventory_item_id
AND NVL(mta.cost_element_id,-99)= NVL(p_cost_element_id,-99)
AND mta.accounting_line_type = 1
AND mta.primary_quantity = decode(l_source_flag,1,l_mmt_primary_quantity,((-1)*l_mmt_primary_quantity))
AND mta.transaction_source_type_id = 13; --ALWAYS INV
Select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(pjm_clearing_account),-999999)
INTO O_cr_code_combination_id
FROM pjm_org_parameters
where organization_id = l_mta_organization_id;
select (decode(sign(l_mmt_primary_quantity),1,10,9))
into l_accounting_line_type
from dual;
SELECT
decode(l_transaction_action_id, 3, --Direct IO Xfer
decode(sign(l_mmt_primary_quantity), 1,9, --IO Pyble
10), --IO Rcvble
32, 7, --Assy Return
31, 7, --Assy Complete
33, 7, --Neg WIP Issue
34, 7, --Neg WIP Return
2, decode(l_xfer_exp_flag, 0,1, 2), --Sub Xfer
4, 2, --Cycle Count
8, 2, --Phy Inv
12, --Intransit Receipt
decode(nvl(l_fob_point,-99), 1,14, 2,9), 21, --Intransit Shipment
decode(nvl(l_fob_point,-99), 1,10, 2,14), 29, decode(l_transaction_source_type_id, 1,5), --PO Adj
1, decode(l_transaction_source_type_id, 1, 5, --RTV
3, 2, --A/C Issue
5, 7, --WIP/CFM
6, 2, --Ali Issue
13,2), --Misc Issue
27, decode(l_transaction_source_type_id, 1, 5, --PO Receipt
3, 2, --A/C Receipt
5, 7, --WIP/CFM
6, 2, --Ali Receipt
13,2), --Misc Rcpt
6,16, /*consigned ownership transfer */
26,31, /* Logical PO receipt for BP org */
28,1, /* staging Txfrs */
17, 9, /* Logical Expense Requition Receipt */ -99)
INTO l_accounting_line_type
FROM DUAL;
Select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(pjm_clearing_account),-999999)
INTO O_cr_code_combination_id
FROM pjm_org_parameters
where organization_id = l_mta_organization_id;
SELECT NVL(MAX(mta.reference_account), -999999)
INTO O_cr_code_combination_id
FROM mtl_transaction_accounts mta
WHERE
mta.transaction_id =l_mta_transaction_id AND
mta.organization_id =l_mta_organization_id AND
mta.inventory_item_id =l_inventory_item_id AND
nvl(mta.cost_element_id, -99) =decode(l_cost_element_id,-99,nvl(mta.cost_element_id,-99),l_cost_element_id) AND
nvl(mta.resource_id, -99) = nvl(p_resource_id,-99) AND
((mta.accounting_line_type = l_accounting_line_type)
OR (mta.accounting_line_type = 31 AND l_accounting_line_type = 5)) AND /* Changes for Global Procurement PO clearing account*/
mta.primary_quantity = l_mta_primary_quantity;
SELECT MAX(INV_SUB_LEDGER_ID)
INTO O_inv_cr_sub_ledger_id
FROM mtl_transaction_accounts mta
WHERE
mta.reference_account = O_cr_code_combination_id AND
mta.transaction_id =l_mta_transaction_id AND
mta.organization_id =l_mta_organization_id AND
mta.inventory_item_id =l_inventory_item_id AND
nvl(mta.cost_element_id, -99) =decode(l_cost_element_id,-99,nvl(mta.cost_element_id,-99),l_cost_element_id) AND
nvl(mta.resource_id, -99) = nvl(p_resource_id,-99) AND
((mta.accounting_line_type = l_accounting_line_type)
OR (mta.accounting_line_type = 31 AND l_accounting_line_type = 5)) AND /* Changes for Global Procurement PO clearing account*/
mta.primary_quantity = l_mta_primary_quantity;
| it will manage the insertion of records in PA_TRANSACTIONS_INTERFACE |
| |
| PARAMETERS |
| |
| |
| CALLED FROM |
| pm_process_txn_mmt |
| |
| HISTORY |
| 23-APR-01 Hemant Gosain Created. |
*----------------------------------------------------------------------------*/
PROCEDURE pm_insert_pti_pvt
(p_transaction_source VARCHAR2,
p_batch_name VARCHAR2,
p_expenditure_ending_date DATE,
p_employee_number VARCHAR2,
p_organization_name VARCHAR2,
p_expenditure_item_date DATE,
p_project_number VARCHAR2,
p_task_number VARCHAR2,
p_expenditure_type VARCHAR2,
p_pa_quantity NUMBER,
p_raw_cost NUMBER,
p_expenditure_comment VARCHAR2,
p_orig_transaction_reference VARCHAR2,
p_raw_cost_rate NUMBER,
p_unmatched_negative_txn_flag VARCHAR2,
p_gl_date DATE,
p_org_id NUMBER,
p_burdened_cost NUMBER,
p_burdened_cost_rate NUMBER,
p_system_linkage VARCHAR2,
p_transaction_status_code VARCHAR2,
p_denom_currency_code VARCHAR2,
p_transaction_id NUMBER,
p_transaction_action_id NUMBER,
p_transaction_source_type_id NUMBER,
p_organization_id NUMBER,
p_inventory_item_id NUMBER,
p_cost_element_id NUMBER,
p_resource_id NUMBER,
p_source_flag NUMBER,
p_variance_flag NUMBER,
p_primary_quantity NUMBER,
p_transfer_organization_id NUMBER,
p_fob_point NUMBER,
p_wip_entity_id NUMBER,
p_basis_resource NUMBER,
p_type_class NUMBER,
p_project_id NUMBER,
p_task_id NUMBER,
p_transaction_date DATE,
p_cost_group_id NUMBER,
p_transfer_cost_group_id NUMBER,
p_transaction_source_id NUMBER,
p_to_project_id NUMBER,
p_to_task_id NUMBER,
p_source_project_id NUMBER,
p_source_task_id NUMBER,
p_transfer_transaction_id NUMBER,
p_primary_cost_method NUMBER,
p_acct_period_id NUMBER,
p_exp_org_id NUMBER,
p_distribution_account_id NUMBER,
p_proj_job_ind NUMBER,
p_first_matl_se_exp_type VARCHAR2,
p_inv_txn_source_literal VARCHAR2,
p_cap_txn_source_literal VARCHAR2,
p_inv_syslink_literal VARCHAR2,
p_bur_syslink_literal VARCHAR2,
p_wip_syslink_literal VARCHAR2,
p_user_def_exp_type VARCHAR2,
p_flow_schedule VARCHAR2,
p_si_asset_yes_no NUMBER,
p_transfer_si_asset_yes_no NUMBER,
O_err_num OUT NOCOPY NUMBER,
O_err_code OUT NOCOPY VARCHAR2,
O_err_msg OUT NOCOPY VARCHAR2
)
IS
l_err_num NUMBER := 0;
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Reached#:pm_insert_pti_pvt');
select to_number(org_information3)
into l_op_unit
from hr_organization_information hoi,
mtl_material_transactions mmt
where hoi.organization_id = mmt.organization_id
and mmt.transaction_id = to_number(p_orig_transaction_reference)
and org_information_context ='Accounting Information';
SELECT nvl(comms_nl_trackable_flag, 'N'), asset_creation_code
INTO l_nl_trackable, l_asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT HAOUT.name
INTO l_business_group_name
FROM hr_all_organization_units_tl HAOUT
WHERE HAOUT.organization_id =
(SELECT COD.business_group_id
FROM cst_organization_definitions COD
WHERE COD.organization_id = p_organization_id)
AND haout.language = USERENV('LANG');
FND_FILE.PUT_LINE(FND_FILE.LOG,'DEBUG: Inserting transaction into PTI ');
/* The following insert statement into pa_transaction_interface will be changed to insert into pa_transaction_interface_all */
/* The following insert statement into pa_transaction_interface will be changed to insert into pa_transaction_interface_all */
/* Modified the insert to insert the Inventory_item_id and basic UOM code bug 3298023 */
/*Get the primary UOM code of the item from mtl_system_items */
select primary_uom_code into l_uom_code
from mtl_system_items msi
where msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id;
INSERT INTO pa_transaction_interface_all
( transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
denom_raw_cost,
acct_raw_cost,
expenditure_comment,
orig_transaction_reference,
raw_cost_rate,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
cdl_system_reference1,
cdl_system_reference2,
cdl_system_reference3,
gl_date,
org_id,
denom_burdened_cost,
acct_burdened_cost,
burdened_cost_rate,
system_linkage,
transaction_status_code,
denom_currency_code,
acct_exchange_rate,
acct_rate_type,
acct_rate_date,
Inventory_item_id,
unit_of_measure,
cdl_system_reference4, --Credit INV Sub ledger ID
cdl_system_reference5, --Debit INV Subledger ID
person_business_group_name
)
VALUES
(
p_transaction_source,
p_batch_name,
p_expenditure_ending_date,
p_employee_number,
p_organization_name,
p_expenditure_item_date,
p_project_number,
p_task_number,
p_expenditure_type,
p_pa_quantity,
decode(l_cc_rate, null, null, (l_raw_cost/l_cc_rate)), /*Foreign currency value*/
l_raw_cost, /*Functional currency value*/
p_expenditure_comment,
p_orig_transaction_reference,
l_raw_cost_rate,
p_unmatched_negative_txn_flag,
l_dr_code_combination_id,
l_cr_code_combination_id,
NULL,
NULL,
NULL,
p_gl_date,
l_op_unit,
decode(l_cc_rate, null, null, (l_burdened_cost/l_cc_rate)), /*Foreign currency value*/
l_burdened_cost, /*Functional currency value */
l_burdened_cost_rate,
l_systemlinkage,
p_transaction_status_code,
l_cc_foreign_currency, /*This should be null for non-foreign currency transactions*/
l_cc_rate,
l_cc_type,
l_cc_date,
p_inventory_item_id,
l_uom_code,
l_inv_cr_sub_ledger_id,
l_inv_dr_sub_ledger_id,
l_business_group_name
);
O_err_code := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
|| 'FAILED CSE Package Call. '
|| l_cse_err_code,1,240);
O_err_code := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
|| 'FAILED TO GET ACCT. '
|| l_err_msg,1,240);
O_err_code := 'CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): ';
O_err_msg := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
||to_char(l_stmt_num)
||'), '
||'CSTCCHKB.pm_invtxn_hook: '
||'error at CSTCCHKB, line: '
||to_char(l_err_num)
||' CSTCCHKB.err_msg:'
||l_err_msg,1,240);
O_err_msg := SUBSTR('CSTPPCCB.pm_insert_pti_pvt('
|| to_char(l_stmt_num)
|| '): '
||SQLERRM,1,240);
END pm_insert_pti_pvt;