[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_NUMBER(NVL( REPLACE(SUBSTRB(USERENV('CLIENT_INFO'),1,10),' '),-99))
INTO l_client_info_org_id
FROM dual;
SELECT TO_NUMBER(NVL( REPLACE(SUBSTRB(USERENV('CLIENT_INFO'),45,10),' '),-99))
INTO l_client_info_rsob_id
FROM dual;
select set_of_books_id
into var.books_id
from ar_system_parameters_all
where org_id = var.reporting_entity_id;
'arrx_tx.bind(:CURSOR_SELECT);',
SELECT_BILL_NUMBER varchar2(500);
select TAX_HEADER_LEVEL_FLAG
into var.tax_header_level_flag
from AR_SYSTEM_PARAMETERS_MRC_V;
select CHART_OF_ACCOUNTS_ID
,NAME
,CURRENCY_CODE
into var.chart_of_accounts_id
,var.organization_name
,var.functional_currency_code
from GL_SETS_OF_BOOKS
where SET_OF_BOOKS_ID = var.books_id;
REC_ACCOUNT_WHERE := ' AND RECDIST.CUSTOMER_TRX_ID in (select RECDIST1.CUSTOMER_TRX_ID from RA_CUST_TRX_LINE_GL_DIST_ALL RECDIST1,GL_CODE_COMBINATIONS CCRECDIST1
where CT.CUSTOMER_TRX_ID = RECDIST.CUSTOMER_TRX_ID AND RECDIST1.CODE_COMBINATION_ID = CCRECDIST1.CODE_COMBINATION_ID AND RECDIST1.ACCOUNT_CLASS in (''REV'',''UNEARN'',''UNBILL'') AND '||
FA_RX_FLEX_PKG.FLEX_SQL(
p_application_id => 101,
p_id_flex_code => 'GL#',
p_id_flex_num => var.chart_of_accounts_id,
p_table_alias => 'CCRECDIST1',
p_mode => 'WHERE',
p_qualifier => 'ALL',
p_function => OPER,
p_operand1 => OP1,
p_operand2 => OP2);
REC_ACCOUNT_WHERE := ' AND RECDIST.CUSTOMER_TRX_ID in (select RECDIST1.CUSTOMER_TRX_ID from RA_TRX_LINE_GL_DIST_ALL_MRC_V RECDIST1,GL_CODE_COMBINATIONS CCRECDIST1
where CT.CUSTOMER_TRX_ID = RECDIST.CUSTOMER_TRX_ID AND RECDIST1.CODE_COMBINATION_ID = CCRECDIST1.CODE_COMBINATION_ID AND RECDIST1.ACCOUNT_CLASS in (''REV'',''UNEARN'',''UNBILL'') AND '||
FA_RX_FLEX_PKG.FLEX_SQL(
p_application_id => 101,
p_id_flex_code => 'GL#',
p_id_flex_num => var.chart_of_accounts_id,
p_table_alias => 'CCRECDIST1',
p_mode => 'WHERE',
p_qualifier => 'ALL',
p_function => OPER,
p_operand1 => OP1,
p_operand2 => OP2);
'in (select ' ||
'org_id, batch_source_id ' ||
'from ra_batch_sources BS ' ||
'where name = :batch_source_name ' ||
' ) ';
SELECT_BILL_NUMBER := null;
SELECT_BILL_NUMBER := 'DECODE(SYSPARAM.SHOW_BILLING_NUMBER_FLAG,''Y'',ARRX_TX.GET_CONS_BILL_NUMBER(CT.CUSTOMER_TRX_ID),NULL) ';
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
fa_rx_util_pkg.debug('ARTX_ASSIGN_SELECT_LIST');
fa_rx_util_pkg.assign_column('60 ',SELECT_BILL_NUMBER, 'CONS_BILL_NUMBER', 'arrx_tx.var.cons_bill_number', 'VARCHAR2', 30);
fa_rx_util_pkg.assign_column('150','ARRX_TX.LAST_UPDATED_BY(CT.CUSTOMER_TRX_ID)', 'TRX_LAST_UPDATED_BY', 'arrx_tx.var.trx_last_updated_by', 'NUMBER');
fa_rx_util_pkg.assign_column('160','ARRX_TX.LAST_UPDATE_DATE(CT.CUSTOMER_TRX_ID)','TRX_LAST_UPDATE_DATE', 'arrx_tx.var.trx_last_update_date', 'DATE');
select MEANING into YES_NO_Y from ar_lookups
where lookup_type = 'YES/NO' and LOOKUP_CODE = 'Y';
select MEANING into YES_NO_N from ar_lookups
where lookup_type = 'YES/NO' and LOOKUP_CODE = 'N';
start_update_date in date,
end_update_date in date,
last_updated_by in number,
request_id in number,
retcode out NOCOPY number,
errbuf out NOCOPY varchar2)
is
-- Document sequence parameter declarations
doc_sequence_name varchar2(30) := NULL;
var.start_update_date := Trunc(start_update_date);
var.end_update_date := Trunc(end_update_date)+1-1/24/60/60;
var.last_updated_by := last_updated_by;
fa_rx_util_pkg.debug('start_update_date = '|| var.start_update_date);
fa_rx_util_pkg.debug('end_update_date = '|| var.end_update_date);
fa_rx_util_pkg.debug('last_updated_by = '|| var.last_updated_by);
'arrx_tx.check_bind(:CURSOR_SELECT);',
LAST_UPDATE_WHERE varchar2(500);
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
IF var.last_updated_by is null THEN
LAST_UPDATE_WHERE := ' AND ARRX_TX.WHERE_LAST_UPDATE(CT.CUSTOMER_TRX_ID,null,
:start_update_date,:end_update_date) = ''Y'' ';
LAST_UPDATE_WHERE := ' AND ARRX_TX.WHERE_LAST_UPDATE(CT.CUSTOMER_TRX_ID,'|| var.last_updated_by ||',
:start_update_date,:end_update_date) = ''Y'' ';
LAST_UPDATE_WHERE;
dbms_sql.bind_variable(c, 'start_update_date', var.start_update_date);
dbms_sql.bind_variable(c, 'end_update_date', var.end_update_date);
'arrx_tx.forecast_bind(:CURSOR_SELECT);',
fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
'arrx_tx.sales_bind(:CURSOR_SELECT);',
LAST_UPDATE_WHERE varchar2(500);
line_select_statement varchar2(1000); -- where-clause statement for sub-query of line information
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'ALL');
p_mode => 'SELECT',
p_qualifier => 'GL_BALANCING');
p_mode => 'SELECT',
p_qualifier => 'GL_ACCOUNT');
p_table_alias => 'CCDIST', -- This alias is used in main select statement
p_mode => 'WHERE',
p_qualifier => 'ALL',
p_function => '<=',
p_operand1 => var.end_account);
p_table_alias => 'CCDIST', -- This alias is used in main select statement
p_mode => 'WHERE',
p_qualifier => 'ALL',
p_function => '>=',
p_operand1 => var.start_account);
p_table_alias => 'CCDIST', -- This alias is used in main select statement
p_mode => 'WHERE',
p_qualifier => 'ALL',
p_function => 'BETWEEN',
p_operand1 => var.start_account,
p_operand2 => var.end_account);
fa_rx_util_pkg.debug('AR_ADD_SELECT_COLUMNS');
line_select_statement :=
' AND CT.CUSTOMER_TRX_ID in '||
'(select distinct line.customer_trx_id
from ra_cust_trx_line_gl_dist linedist,
ra_customer_trx_lines line,
gl_code_combinations linegl
where linedist.account_class <> ''REC''
and linedist.customer_trx_line_id = line.customer_trx_line_id
and linedist.code_combination_id = linegl.code_combination_id
and linedist.account_set_flag = ''N'''||
account_where ||
amount_where ||')';
line_select_statement := null;
line_select_statement ||
transaction_number_where ||
natural_account_where ||'
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
AND ITEM.ORGANIZATION_ID(+) = '|| var.so_organization_id ||'
AND CTL.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID(+)
AND CTL.MEMO_LINE_ID = MEMO.MEMO_LINE_ID(+)
AND CT.CUSTOMER_TRX_ID = DIST.CUSTOMER_TRX_ID
AND CTL.CUSTOMER_TRX_LINE_ID = DIST.CUSTOMER_TRX_LINE_ID
AND DIST.ACCOUNT_SET_FLAG = ''N''
AND DIST.CODE_COMBINATION_ID = CCDIST.CODE_COMBINATION_ID ';
SELECT CONS_INV.CONS_BILLING_NUMBER
FROM AR_PAYMENT_SCHEDULES PS, AR_CONS_INV CONS_INV
WHERE PS.CONS_INV_ID = CONS_INV.CONS_INV_ID
AND PS.STATUS = 'OP'
AND PS.CUSTOMER_TRX_ID = CTID;
procedure GET_LAST_UPDATE(P_CUSTOMER_TRX_ID in number)
is
cursor H is
select last_update_date,last_updated_by
from ra_customer_trx
where customer_trx_id = P_CUSTOMER_TRX_ID
order by last_update_date desc;
select last_update_date,last_updated_by
from ra_customer_trx_lines
where customer_trx_id = P_CUSTOMER_TRX_ID
order by last_update_date desc;
select last_update_date,last_updated_by
from ra_cust_trx_line_gl_dist
where customer_trx_id = P_CUSTOMER_TRX_ID
and ((account_class = 'REC' and latest_rec_flag = 'Y')
or (account_class <> 'REC' and account_set_flag = 'N'))
order by last_update_date desc;
var.update_date := HEADER_DATE;
var.update_date := LINE_DATE;
var.update_date := DIST_DATE;
END GET_LAST_UPDATE;
function LAST_UPDATED_BY(P_CUSTOMER_TRX_ID in number)
return number
is
begin
IF P_CUSTOMER_TRX_ID <> var.ctid THEN
GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
end LAST_UPDATED_BY;
function LAST_UPDATE_DATE(P_CUSTOMER_TRX_ID in number)
return date
is
begin
IF P_CUSTOMER_TRX_ID <> var.ctid THEN
GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
return var.update_date;
end LAST_UPDATE_DATE;
function WHERE_LAST_UPDATE(P_CUSTOMER_TRX_ID in number, P_LAST_UPDATED_BY in number, P_START_UPDATE_DATE in date, P_END_UPDATE_DATE in date)
return varchar2
is
begin
IF P_CUSTOMER_TRX_ID <> var.ctid THEN
GET_LAST_UPDATE(P_CUSTOMER_TRX_ID);
IF var.update_date BETWEEN P_START_UPDATE_DATE AND P_END_UPDATE_DATE THEN
IF P_LAST_UPDATED_BY IS NULL THEN
return 'Y';
ELSIF P_LAST_UPDATED_BY = var.user_id THEN
return 'Y';
end WHERE_LAST_UPDATE;