[Home] [Help]
3328:
3329: /*****************************************************************************
3330: * This procedure is equivalent to witpsar_auto_resources in wiltps2.ppc
3331: * This procedure is used to insert auto-resources associated with move txn
3332: * This procedure insert the record into WIP_COST_TXN_INTERFACE and
3333: * WIP_TXN_ALLOCATIONS IF needed
3334: ****************************************************************************/
3335: PROCEDURE insert_auto_resource(p_gib IN group_rec_t,
3336: x_returnStatus OUT NOCOPY VARCHAR2) IS
3390: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3391: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3392: /* Fix for bug 4036149: Added leading hint to drive from the table
3393: wip_move_txn_interface to improve performance */
3394: INSERT INTO wip_cost_txn_interface
3395: (transaction_id,
3396: last_update_date,
3397: last_updated_by,
3398: last_updated_by_name,
3595: -- IF debug message level = 2, write statement below to log file
3596: IF (l_logLevel <= wip_constants.full_logging) THEN
3597: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
3598: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
3599: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
3600: l_msg := fnd_message.get;
3601: wip_logger.log(p_msg => l_msg,
3602: x_returnStatus => l_returnStatus);
3603: END IF;
3607: -- Per item basis type for repetitive schedule
3608: IF(l_move.scheTxn = WIP_CONSTANTS.YES AND
3609: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3610:
3611: INSERT INTO wip_cost_txn_interface
3612: (transaction_id,
3613: last_update_date,
3614: last_updated_by,
3615: last_updated_by_name,
3815: -- IF debug message level = 2, write statement below to log file
3816: IF (l_logLevel <= wip_constants.full_logging) THEN
3817: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
3818: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
3819: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
3820: l_msg := fnd_message.get;
3821: wip_logger.log(p_msg => l_msg,
3822: x_returnStatus => l_returnStatus);
3823: END IF;
3832: +------------------------------------------------------------*/
3833: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3834: l_move.rsrcLot = WIP_CONSTANTS.YES) THEN
3835:
3836: INSERT INTO wip_cost_txn_interface
3837: (transaction_id,
3838: last_update_date,
3839: last_updated_by,
3840: last_updated_by_name,
4069: -- IF debug message level = 2, write statement below to log file
4070: IF (l_logLevel <= wip_constants.full_logging) THEN
4071: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4072: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4073: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4074: l_msg := fnd_message.get;
4075: wip_logger.log(p_msg => l_msg,
4076: x_returnStatus => l_returnStatus);
4077: END IF;
4081: -- Per order basis type for repetitive
4082: IF(l_move.scheTxn = WIP_CONSTANTS.YES AND
4083: l_move.rsrcLot = WIP_CONSTANTS.YES) THEN
4084:
4085: INSERT INTO wip_cost_txn_interface
4086: (transaction_id,
4087: last_update_date,
4088: last_updated_by,
4089: last_updated_by_name,
4285: -- IF debug message level = 2, write statement below to log file
4286: IF (l_logLevel <= wip_constants.full_logging) THEN
4287: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4288: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4289: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4290: l_msg := fnd_message.get;
4291: wip_logger.log(p_msg => l_msg,
4292: x_returnStatus => l_returnStatus);
4293: END IF;
4300:
4301: /*------------------------------------------------------------+
4302: | Generate transaction_id for WIP_TXN_ALLOCATIONS |
4303: +------------------------------------------------------------*/
4304: UPDATE wip_cost_txn_interface
4305: SET transaction_id = wip_transactions_s.nextval
4306: WHERE group_id = p_gib.group_id
4307: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
4308: AND transaction_type = WIP_CONSTANTS.RES_TXN;
4310: -- IF debug message level = 2, write statement below to log file
4311: IF (l_logLevel <= wip_constants.full_logging) THEN
4312: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
4313: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4314: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4315: l_msg := fnd_message.get;
4316: wip_logger.log(p_msg => l_msg,
4317: x_returnStatus => l_returnStatus);
4318: END IF;
4407:
4408: FROM wip_operation_resources wor,
4409: wip_operations wop,
4410: wip_move_txn_allocations wma,
4411: wip_cost_txn_interface wci,
4412: wip_move_txn_interface wmti
4413: WHERE wmti.group_id = p_gib.group_id
4414: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4415: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4560: -1,-1) -- primary_quantity
4561: FROM wip_operation_resources wor,
4562: wip_move_txn_allocations wmta,
4563: wip_move_txn_interface wmti,
4564: wip_cost_txn_interface wcti
4565: WHERE wcti.group_id = p_gib.group_id
4566: AND wcti.process_phase = WIP_CONSTANTS.RES_PROC
4567: AND wcti.process_status = WIP_CONSTANTS.PENDING
4568: AND wcti.transaction_type = WIP_CONSTANTS.RES_TXN
4621:
4622: /*****************************************************************************
4623: * This procedure is equivalent to witpsdo_dept_overheads in wiltps3.ppc
4624: * This procedure is used to insert department overhead info into
4625: * WIP_COST_TXN_INTERFACE and WIP_TXN_ALLOCATIONS IF needed
4626: ****************************************************************************/
4627: PROCEDURE insert_dept_overhead(p_gib IN group_rec_t,
4628: x_returnStatus OUT NOCOPY VARCHAR2) IS
4629:
4680:
4681: -- Per item basis type for discrete jobs
4682: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
4683:
4684: INSERT INTO wip_cost_txn_interface
4685: (transaction_id,
4686: last_update_date,
4687: last_updated_by,
4688: last_updated_by_name,
4897: -- IF debug message level = 2, write statement below to log file
4898: IF (l_logLevel <= wip_constants.full_logging) THEN
4899: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4900: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4901: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4902: l_msg := fnd_message.get;
4903: wip_logger.log(p_msg => l_msg,
4904: x_returnStatus => l_returnStatus);
4905: END IF;
4908:
4909: -- Per item basis type for repetitive schedule
4910: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
4911:
4912: INSERT INTO wip_cost_txn_interface
4913: (transaction_id,
4914: last_update_date,
4915: last_updated_by,
4916: last_updated_by_name,
5107: -- IF debug message level = 2, write statement below to log file
5108: IF (l_logLevel <= wip_constants.full_logging) THEN
5109: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
5110: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5111: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5112: l_msg := fnd_message.get;
5113: wip_logger.log(p_msg => l_msg,
5114: x_returnStatus => l_returnStatus);
5115: END IF;
5125: | We have more than one record per op_seq because of quantity_completed
5126: +----------------------------------------------------------------------*/
5127: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
5128:
5129: INSERT INTO wip_cost_txn_interface
5130: (transaction_id,
5131: last_update_date,
5132: last_updated_by,
5133: last_updated_by_name,
5371: -- IF debug message level = 2, write statement below to log file
5372: IF (l_logLevel <= wip_constants.full_logging) THEN
5373: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
5374: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5375: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5376: l_msg := fnd_message.get;
5377: wip_logger.log(p_msg => l_msg,
5378: x_returnStatus => l_returnStatus);
5379: END IF;
5382:
5383: -- Per order basis type for repetitive
5384: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
5385:
5386: INSERT INTO wip_cost_txn_interface
5387: (transaction_id,
5388: last_update_date,
5389: last_updated_by,
5390: last_updated_by_name,
5577: -- IF debug message level = 2, write statement below to log file
5578: IF (l_logLevel <= wip_constants.full_logging) THEN
5579: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
5580: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5581: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5582: l_msg := fnd_message.get;
5583: wip_logger.log(p_msg => l_msg,
5584: x_returnStatus => l_returnStatus);
5585: END IF;
5589:
5590: /*------------------------------------------------------------+
5591: | Generate transaction_id for WIP_TXN_ALLOCATIONS |
5592: +------------------------------------------------------------*/
5593: UPDATE wip_cost_txn_interface
5594: SET transaction_id = wip_transactions_s.nextval
5595: WHERE group_id = p_gib.group_id
5596: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
5597: AND transaction_type = WIP_CONSTANTS.OVHD_TXN;
5599: -- IF debug message level = 2, write statement below to log file
5600: IF (l_logLevel <= wip_constants.full_logging) THEN
5601: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
5602: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5603: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5604: l_msg := fnd_message.get;
5605: wip_logger.log(p_msg => l_msg,
5606: x_returnStatus => l_returnStatus);
5607: END IF;
5689: 1, DECODE(SIGN(MAX(wop.quantity_completed)),1,0,1),
5690: -1, DECODE(SIGN(MAX(wop.quantity_completed)),1,-1,0))
5691: FROM wip_move_txn_allocations wma,
5692: wip_operations wop,
5693: wip_cost_txn_interface wci,
5694: wip_move_txn_interface wmti
5695: WHERE wmti.group_id = p_gib.group_id
5696: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5697: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5751: END insert_dept_overhead;
5752:
5753: /*****************************************************************************
5754: * This procedure is equivalent to witpsrt_release_cost_txns in wiltps3.ppc
5755: * This procedure is used to set group_id in wip_cost_txn_interface to be NULL
5756: * so that Costing Manager will pick up the records.
5757: ****************************************************************************/
5758: PROCEDURE release_cost_txn(p_gib IN group_rec_t,
5759: x_returnStatus OUT NOCOPY VARCHAR2) IS
5775: p_params => l_params,
5776: x_returnStatus => l_returnStatus);
5777: END IF;
5778:
5779: UPDATE wip_cost_txn_interface
5780: SET group_id = NULL
5781: WHERE group_id = p_gib.group_id
5782: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date);
5783:
5784: -- IF debug message level = 2, write statement below to log file
5785: IF (l_logLevel <= wip_constants.full_logging) THEN
5786: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
5787: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5788: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5789: l_msg := fnd_message.get;
5790: wip_logger.log(p_msg => l_msg,
5791: x_returnStatus => l_returnStatus);
5792: END IF;
9709: END IF; -- check return status
9710:
9711: -- Delete child record (overmove/overcompletion/overreturn)
9712: -- Insert auto-resources associated with move
9713: -- (insert into WIP_COST_TXN_INTERFACE)
9714: insert_auto_resource(p_gib => p_gib,
9715: x_returnStatus => x_returnStatus);
9716:
9717: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
9732: raise fnd_api.g_exc_unexpected_error;
9733: END IF; -- check return status
9734: END IF; -- repetitive schedule and resource per item exist
9735:
9736: -- Insert department overhead into WIP_COST_TXN_INTERFACE
9737: insert_dept_overhead(p_gib => p_gib,
9738: x_returnStatus => x_returnStatus);
9739:
9740: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN