[Home] [Help]
3199:
3200: /*****************************************************************************
3201: * This procedure is equivalent to witpsar_auto_resources in wiltps2.ppc
3202: * This procedure is used to insert auto-resources associated with move txn
3203: * This procedure insert the record into WIP_COST_TXN_INTERFACE and
3204: * WIP_TXN_ALLOCATIONS IF needed
3205: ****************************************************************************/
3206: PROCEDURE insert_auto_resource(p_gib IN group_rec_t,
3207: x_returnStatus OUT NOCOPY VARCHAR2) IS
3261: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3262: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3263: /* Fix for bug 4036149: Added leading hint to drive from the table
3264: wip_move_txn_interface to improve performance */
3265: INSERT INTO wip_cost_txn_interface
3266: (transaction_id,
3267: last_update_date,
3268: last_updated_by,
3269: last_updated_by_name,
3466: -- IF debug message level = 2, write statement below to log file
3467: IF (l_logLevel <= wip_constants.full_logging) THEN
3468: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
3469: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
3470: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
3471: l_msg := fnd_message.get;
3472: wip_logger.log(p_msg => l_msg,
3473: x_returnStatus => l_returnStatus);
3474: END IF;
3478: -- Per item basis type for repetitive schedule
3479: IF(l_move.scheTxn = WIP_CONSTANTS.YES AND
3480: l_move.rsrcItem = WIP_CONSTANTS.YES) THEN
3481:
3482: INSERT INTO wip_cost_txn_interface
3483: (transaction_id,
3484: last_update_date,
3485: last_updated_by,
3486: last_updated_by_name,
3686: -- IF debug message level = 2, write statement below to log file
3687: IF (l_logLevel <= wip_constants.full_logging) THEN
3688: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
3689: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
3690: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
3691: l_msg := fnd_message.get;
3692: wip_logger.log(p_msg => l_msg,
3693: x_returnStatus => l_returnStatus);
3694: END IF;
3703: +------------------------------------------------------------*/
3704: IF(l_move.jobTxn = WIP_CONSTANTS.YES AND
3705: l_move.rsrcLot = WIP_CONSTANTS.YES) THEN
3706:
3707: INSERT INTO wip_cost_txn_interface
3708: (transaction_id,
3709: last_update_date,
3710: last_updated_by,
3711: last_updated_by_name,
3940: -- IF debug message level = 2, write statement below to log file
3941: IF (l_logLevel <= wip_constants.full_logging) THEN
3942: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
3943: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
3944: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
3945: l_msg := fnd_message.get;
3946: wip_logger.log(p_msg => l_msg,
3947: x_returnStatus => l_returnStatus);
3948: END IF;
3952: -- Per order basis type for repetitive
3953: IF(l_move.scheTxn = WIP_CONSTANTS.YES AND
3954: l_move.rsrcLot = WIP_CONSTANTS.YES) THEN
3955:
3956: INSERT INTO wip_cost_txn_interface
3957: (transaction_id,
3958: last_update_date,
3959: last_updated_by,
3960: last_updated_by_name,
4156: -- IF debug message level = 2, write statement below to log file
4157: IF (l_logLevel <= wip_constants.full_logging) THEN
4158: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4159: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4160: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4161: l_msg := fnd_message.get;
4162: wip_logger.log(p_msg => l_msg,
4163: x_returnStatus => l_returnStatus);
4164: END IF;
4171:
4172: /*------------------------------------------------------------+
4173: | Generate transaction_id for WIP_TXN_ALLOCATIONS |
4174: +------------------------------------------------------------*/
4175: UPDATE wip_cost_txn_interface
4176: SET transaction_id = wip_transactions_s.nextval
4177: WHERE group_id = p_gib.group_id
4178: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
4179: AND transaction_type = WIP_CONSTANTS.RES_TXN;
4181: -- IF debug message level = 2, write statement below to log file
4182: IF (l_logLevel <= wip_constants.full_logging) THEN
4183: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
4184: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4185: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4186: l_msg := fnd_message.get;
4187: wip_logger.log(p_msg => l_msg,
4188: x_returnStatus => l_returnStatus);
4189: END IF;
4278:
4279: FROM wip_operation_resources wor,
4280: wip_operations wop,
4281: wip_move_txn_allocations wma,
4282: wip_cost_txn_interface wci,
4283: wip_move_txn_interface wmti
4284: WHERE wmti.group_id = p_gib.group_id
4285: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
4286: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
4431: -1,-1) -- primary_quantity
4432: FROM wip_operation_resources wor,
4433: wip_move_txn_allocations wmta,
4434: wip_move_txn_interface wmti,
4435: wip_cost_txn_interface wcti
4436: WHERE wcti.group_id = p_gib.group_id
4437: AND wcti.process_phase = WIP_CONSTANTS.RES_PROC
4438: AND wcti.process_status = WIP_CONSTANTS.PENDING
4439: AND wcti.transaction_type = WIP_CONSTANTS.RES_TXN
4492:
4493: /*****************************************************************************
4494: * This procedure is equivalent to witpsdo_dept_overheads in wiltps3.ppc
4495: * This procedure is used to insert department overhead info into
4496: * WIP_COST_TXN_INTERFACE and WIP_TXN_ALLOCATIONS IF needed
4497: ****************************************************************************/
4498: PROCEDURE insert_dept_overhead(p_gib IN group_rec_t,
4499: x_returnStatus OUT NOCOPY VARCHAR2) IS
4500:
4551:
4552: -- Per item basis type for discrete jobs
4553: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
4554:
4555: INSERT INTO wip_cost_txn_interface
4556: (transaction_id,
4557: last_update_date,
4558: last_updated_by,
4559: last_updated_by_name,
4768: -- IF debug message level = 2, write statement below to log file
4769: IF (l_logLevel <= wip_constants.full_logging) THEN
4770: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4771: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4772: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4773: l_msg := fnd_message.get;
4774: wip_logger.log(p_msg => l_msg,
4775: x_returnStatus => l_returnStatus);
4776: END IF;
4779:
4780: -- Per item basis type for repetitive schedule
4781: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
4782:
4783: INSERT INTO wip_cost_txn_interface
4784: (transaction_id,
4785: last_update_date,
4786: last_updated_by,
4787: last_updated_by_name,
4978: -- IF debug message level = 2, write statement below to log file
4979: IF (l_logLevel <= wip_constants.full_logging) THEN
4980: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
4981: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
4982: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
4983: l_msg := fnd_message.get;
4984: wip_logger.log(p_msg => l_msg,
4985: x_returnStatus => l_returnStatus);
4986: END IF;
4996: | We have more than one record per op_seq because of quantity_completed
4997: +----------------------------------------------------------------------*/
4998: IF(l_move.jobTxn = WIP_CONSTANTS.YES) THEN
4999:
5000: INSERT INTO wip_cost_txn_interface
5001: (transaction_id,
5002: last_update_date,
5003: last_updated_by,
5004: last_updated_by_name,
5242: -- IF debug message level = 2, write statement below to log file
5243: IF (l_logLevel <= wip_constants.full_logging) THEN
5244: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
5245: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5246: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5247: l_msg := fnd_message.get;
5248: wip_logger.log(p_msg => l_msg,
5249: x_returnStatus => l_returnStatus);
5250: END IF;
5253:
5254: -- Per order basis type for repetitive
5255: IF(l_move.scheTxn = WIP_CONSTANTS.YES) THEN
5256:
5257: INSERT INTO wip_cost_txn_interface
5258: (transaction_id,
5259: last_update_date,
5260: last_updated_by,
5261: last_updated_by_name,
5448: -- IF debug message level = 2, write statement below to log file
5449: IF (l_logLevel <= wip_constants.full_logging) THEN
5450: fnd_message.set_name('WIP', 'WIP_INSERTED_ROWS');
5451: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5452: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5453: l_msg := fnd_message.get;
5454: wip_logger.log(p_msg => l_msg,
5455: x_returnStatus => l_returnStatus);
5456: END IF;
5460:
5461: /*------------------------------------------------------------+
5462: | Generate transaction_id for WIP_TXN_ALLOCATIONS |
5463: +------------------------------------------------------------*/
5464: UPDATE wip_cost_txn_interface
5465: SET transaction_id = wip_transactions_s.nextval
5466: WHERE group_id = p_gib.group_id
5467: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date)
5468: AND transaction_type = WIP_CONSTANTS.OVHD_TXN;
5470: -- IF debug message level = 2, write statement below to log file
5471: IF (l_logLevel <= wip_constants.full_logging) THEN
5472: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
5473: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5474: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5475: l_msg := fnd_message.get;
5476: wip_logger.log(p_msg => l_msg,
5477: x_returnStatus => l_returnStatus);
5478: END IF;
5560: 1, DECODE(SIGN(MAX(wop.quantity_completed)),1,0,1),
5561: -1, DECODE(SIGN(MAX(wop.quantity_completed)),1,-1,0))
5562: FROM wip_move_txn_allocations wma,
5563: wip_operations wop,
5564: wip_cost_txn_interface wci,
5565: wip_move_txn_interface wmti
5566: WHERE wmti.group_id = p_gib.group_id
5567: AND TRUNC(wmti.transaction_date) = TRUNC(p_gib.txn_date)
5568: AND wmti.process_phase = WIP_CONSTANTS.MOVE_PROC
5622: END insert_dept_overhead;
5623:
5624: /*****************************************************************************
5625: * This procedure is equivalent to witpsrt_release_cost_txns in wiltps3.ppc
5626: * This procedure is used to set group_id in wip_cost_txn_interface to be NULL
5627: * so that Costing Manager will pick up the records.
5628: ****************************************************************************/
5629: PROCEDURE release_cost_txn(p_gib IN group_rec_t,
5630: x_returnStatus OUT NOCOPY VARCHAR2) IS
5646: p_params => l_params,
5647: x_returnStatus => l_returnStatus);
5648: END IF;
5649:
5650: UPDATE wip_cost_txn_interface
5651: SET group_id = NULL
5652: WHERE group_id = p_gib.group_id
5653: AND TRUNC(transaction_date) = TRUNC(p_gib.txn_date);
5654:
5655: -- IF debug message level = 2, write statement below to log file
5656: IF (l_logLevel <= wip_constants.full_logging) THEN
5657: fnd_message.set_name('WIP', 'WIP_UPDATED_ROWS');
5658: fnd_message.set_token('ENTITY1', SQL%ROWCOUNT);
5659: fnd_message.set_token('ENTITY2', 'WIP_COST_TXN_INTERFACE');
5660: l_msg := fnd_message.get;
5661: wip_logger.log(p_msg => l_msg,
5662: x_returnStatus => l_returnStatus);
5663: END IF;
9436: END IF; -- check return status
9437:
9438: -- Delete child record (overmove/overcompletion/overreturn)
9439: -- Insert auto-resources associated with move
9440: -- (insert into WIP_COST_TXN_INTERFACE)
9441: insert_auto_resource(p_gib => p_gib,
9442: x_returnStatus => x_returnStatus);
9443:
9444: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN
9459: raise fnd_api.g_exc_unexpected_error;
9460: END IF; -- check return status
9461: END IF; -- repetitive schedule and resource per item exist
9462:
9463: -- Insert department overhead into WIP_COST_TXN_INTERFACE
9464: insert_dept_overhead(p_gib => p_gib,
9465: x_returnStatus => x_returnStatus);
9466:
9467: IF(x_returnStatus <> fnd_api.g_ret_sts_success) THEN