[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select the bucket pattern from chv_bucket_patterns based on
** incoming parameter bucket_pattern_id including the week
** start day.
** We have to hardcode the date due to nls issue when using the next_day function. We have to use
** a known date to get the correct 3 char day in the current language.
*/
SELECT decode(cbp.week_start_day,'1_MONDAY' , to_char(to_date('31/01/2000','DD/MM/YYYY'),'DY'),
'2_TUESDAY' , to_char(to_date('01/02/2000','DD/MM/YYYY'),'DY'),
'3_WEDNESDAY' , to_char(to_date('02/02/2000','DD/MM/YYYY'),'DY'),
'4_THURSDAY' , to_char(to_date('03/02/2000','DD/MM/YYYY'),'DY'),
'5_FRIDAY' , to_char(to_date('04/02/2000','DD/MM/YYYY'),'DY'),
'6_SATURDAY' , to_char(to_date('05/02/2000','DD/MM/YYYY'),'DY'),
'7_SUNDAY' , to_char(to_date('06/02/2000','DD/MM/YYYY'),'DY'),
to_char(to_date('31/01/2000','DD/MM/YYYY'),'DY')
),
cbp.number_daily_buckets,
cbp.number_weekly_buckets,
cbp.number_monthly_buckets,
cbp.number_quarterly_buckets
INTO x_week_start_day,
x_day_count,
x_week_count,
x_month_count,
x_quarter_count
FROM chv_bucket_patterns cbp
WHERE cbp.bucket_pattern_id = p_bucket_pattern_id;
** This has to be done because the insert_buckets program will fail
** since the PL/SQL tables are transposed into a record and a hard code
** insert is done.
*/
for i in x_bucket_count..60 loop
x_bucket_descriptor_table(x_bucket_count) := null ;
po_message_s.sql_error('Insert_Row', X_progress, sqlcode);
p_row_select_order IN NUMBER,
p_row_type IN VARCHAR2,
p_bucket_table IN BKTTABLE) IS
x_progress varchar2(3) := NULL ;
x_last_updated_by NUMBER ;
x_last_updated_by := fnd_global.user_id ;
/* Insert the record type into the table */
insert into chv_horizontal_schedules
(SCHEDULE_ID,
SCHEDULE_ITEM_ID,
ROW_SELECT_ORDER,
ROW_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
COLUMN5,
COLUMN6,
COLUMN7,
COLUMN8,
COLUMN9,
COLUMN10,
COLUMN11,
COLUMN12,
COLUMN13,
COLUMN14,
COLUMN15,
COLUMN16,
COLUMN17,
COLUMN18,
COLUMN19,
COLUMN20,
COLUMN21,
COLUMN22,
COLUMN23,
COLUMN24,
COLUMN25,
COLUMN26,
COLUMN27,
COLUMN28,
COLUMN29,
COLUMN30,
COLUMN31,
COLUMN32,
COLUMN33,
COLUMN34,
COLUMN35,
COLUMN36,
COLUMN37,
COLUMN38,
COLUMN39,
COLUMN40,
COLUMN41,
COLUMN42,
COLUMN43,
COLUMN44,
COLUMN45,
COLUMN46,
COLUMN47,
COLUMN48,
COLUMN49,
COLUMN50,
COLUMN51,
COLUMN52,
COLUMN53,
COLUMN54,
COLUMN55,
COLUMN56,
COLUMN57,
COLUMN58,
COLUMN59,
COLUMN60,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES(p_schedule_id,
p_schedule_item_id,
p_row_select_order,
p_row_type,
sysdate,
x_last_updated_by,
sysdate,
x_last_updated_by,
p_bucket_table(1),
p_bucket_table(2),
p_bucket_table(3),
p_bucket_table(4),
p_bucket_table(5),
p_bucket_table(6),
p_bucket_table(7),
p_bucket_table(8),
p_bucket_table(9),
p_bucket_table(10),
p_bucket_table(11),
p_bucket_table(12),
p_bucket_table(13),
p_bucket_table(14),
p_bucket_table(15),
p_bucket_table(16),
p_bucket_table(17),
p_bucket_table(18),
p_bucket_table(19),
p_bucket_table(20),
p_bucket_table(21),
p_bucket_table(22),
p_bucket_table(23),
p_bucket_table(24),
p_bucket_table(25),
p_bucket_table(26),
p_bucket_table(27),
p_bucket_table(28),
p_bucket_table(29),
p_bucket_table(30),
p_bucket_table(31),
p_bucket_table(32),
p_bucket_table(33),
p_bucket_table(34),
p_bucket_table(35),
p_bucket_table(36),
p_bucket_table(37),
p_bucket_table(38),
p_bucket_table(39),
p_bucket_table(40),
p_bucket_table(41),
p_bucket_table(42),
p_bucket_table(43),
p_bucket_table(44),
p_bucket_table(45),
p_bucket_table(46),
p_bucket_table(47),
p_bucket_table(48),
p_bucket_table(49),
p_bucket_table(50),
p_bucket_table(51),
p_bucket_table(52),
p_bucket_table(53),
p_bucket_table(54),
p_bucket_table(55),
p_bucket_table(56),
p_bucket_table(57),
p_bucket_table(58),
p_bucket_table(59),
p_bucket_table(60),
x_login_id,
null,
null,
null,
null ) ;
po_message_s.sql_error('Insert_Row', X_progress, sqlcode);
x_row_select_order number ;
/* Calculate Past Due Bucket Quantity by selecting
** total ORDER_QUANTITY from CHV_ITEM_ORDERS
** based on the due_date.
*/
begin
x_progress := '020' ;
select nvl(sum(round(cio.order_quantity,5)),0),
nvl(sum(round(cio.order_quantity_primary,5)),0)
into x_release_quantity,
x_release_quantity_primary
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) <= to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD') ;
/* Calculate Bucket Firm and Forecast Quantity by selecting
** total ORDER_QUANTITY from CHV_ITEM_ORDERS
** based on the due_date.
*/
begin
x_progress := '030' ;
/* Selecting forecast quantities from chv_item_orders
** for the scheduled_item.
*/
select nvl(sum(round(cio.order_quantity,5)),0)
into x_forecast_quantity
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and ((p_schedule_type = 'FORECAST_ALL_DOCUMENTS'
and cio.supply_document_type in ('RELEASE','PLANNED_ORDER','REQUISITION')
and trunc(cio.due_date) between to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD')
and to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD') )
OR
(p_schedule_type in ('FORECAST_ONLY','MATERIAL_RELEASE','RELEASE_WITH_FORECAST')
and cio.supply_document_type in ('PLANNED_ORDER','REQUISITION')
and trunc(cio.due_date) between to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD')
and to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD') )
) ;
/* Selecting release quantities from chv_item_orders
** for the scheduled_item.
*/
select nvl(sum(round(cio.order_quantity,5)),0)
into x_release_quantity
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and p_schedule_type in ('RELEASE_ONLY','MATERIAL_RELEASE','RELEASE_WITH_FORECAST')
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) between to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD')
and to_date(p_bucket_end_date_table(x_bucket_count),'YYYY/MM/DD') ;
/* Select all future releases from CHV_ITEM_ORDERS for the future
** release bucket.
*/
begin
x_progress := '050' ;
select nvl(sum(round(cio.order_quantity,5)),0)
into x_release_quantity
from chv_item_orders cio
where cio.schedule_id = p_schedule_id
and cio.schedule_item_id = p_schedule_item_id
and cio.supply_document_type = 'RELEASE'
and trunc(cio.due_date) > to_date(p_bucket_start_date_table(x_bucket_count),'YYYY/MM/DD') ;
/* Initialize row_select_order and row_type and call stored
** procedure insert_buckets by passing the appropriate PL/SQL table
** to insert the record into CHV_HORIZONTAL_SCHEDULES
*/
x_row_select_order := 1 ;
x_row_select_order ,
x_row_type ,
p_bucket_descriptor_table ) ;
x_row_select_order := 2 ;
x_row_select_order ,
x_row_type ,
p_bucket_start_date_table ) ;
x_row_select_order := 3 ;
x_row_select_order ,
x_row_type ,
x_release_quantity_table ) ;
x_row_select_order := 4 ;
x_row_select_order ,
x_row_type ,
x_forecast_quantity_table ) ;
x_row_select_order := 5 ;
x_row_select_order ,
x_row_type ,
x_total_quantity_table ) ;
x_row_select_order := 6 ;
x_row_select_order ,
x_row_type ,
x_cum_quantity_table ) ;
x_row_select_order := 7 ;
x_row_select_order ,
x_row_type ,
p_bucket_end_date_table ) ;