Formated Query For Auto Month Addition of Given period with all tabs etc for Data Loader

By Using this query you can generate your sequence information in data Loader Format

SELECT SUBSTR (sa.method_code, 1, 1) ty, fap.application_name, ‘\{TAB}’ tb1,

dsc.NAME, ‘\{TAB}’ tb2, sob.NAME, ‘\{TAB}’ tb3,

DECODE (SUBSTR (sa.method_code, 1, 1), ‘A’, ‘\{LEFT}’) lf1,

‘\{LEFT}’ lf2, ‘\{TAB}’ tb4,

TO_CHAR (ADD_MONTHS (sa.start_date, 1), ‘DD-MON-YYYY’) start_dt,

‘\{TAB}’ tb5,

TO_CHAR (ADD_MONTHS (sa.end_date, 1), ‘DD-MON-YYYY’) date_ed,

‘\{TAB}’ tb6,

REPLACE (REPLACE (UPPER (ds.NAME),

TO_CHAR (sa.start_date, ‘MON’),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), ‘MON’)

),

TO_CHAR (sa.start_date, ‘YY’),

TO_CHAR (ADD_MONTHS (sa.start_date, 1), ‘YY’)

) seq_na,

‘\{TAB}’ tb7

FROM fnd_doc_sequence_assignments sa,

fnd_application_vl fap,

gl_sets_of_books sob,

fnd_document_sequences ds,

fnd_doc_sequence_categories dsc

WHERE (sa.start_date >= TO_DATE (’01-01-2007′, ‘DD-MM-YYYY’))

AND (sa.end_date <= TO_DATE (’31-01-2007′, ‘DD-MM-YYYY’))

AND sa.application_id = fap.application_id

AND sa.set_of_books_id = sob.set_of_books_id

AND sa.doc_sequence_id = ds.doc_sequence_id

AND sa.category_code = dsc.code

ORDER BY sob.NAME, sa.category_code, sa.method_code, sa.application_id

Just need to Input Start_date and end_date in my case this is ’01-01-2007 and 31-01-2007′

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s